I am working with Entity Framework version 6 together with SQL Server localdb 13.0.2151.

I have 4 entities Professors, Resources, Subjects and Assignments.

The query I intend to make must retrieve all Assignments along with their Resources and Matters for a given Teacher.

var asignacionesPaginadas = await db.Asignaciones.AsNoTracking()
.Where(a => a.Profesores.ProfesorId==profesorId)
.Include(a => a.Materia)
.Include(a => a.Recurso)
.OrderBy(a => a.FechaHoraReserva)
.Skip(_paginado.CantidadPorPagina * _paginado.PaginaActual)
.Take(_paginado.CantidadPorPagina)
.ToListAsync();

And I do not get the expected results. Using the Profiler I see that it generates:

SELECT 
[Project1].[AsignacionId] AS [AsignacionId], 
[Project1].[FechaHoraReserva] AS [FechaHoraReserva], 
[Project1].[MateriaId] AS [MateriaId], 
[Project1].[Nombre] AS [Nombre], 
[Project1].[Carrera] AS [Carrera], 
[Project1].[RecursoId] AS [RecursoId], 
[Project1].[Descripcion] AS [Descripcion]
FROM ( SELECT 
    [Extent1].[AsignacionId] AS [AsignacionId], 
    [Extent1].[FechaHoraReserva] AS [FechaHoraReserva], 
    [Extent2].[MateriaId] AS [MateriaId], 
    [Extent2].[Nombre] AS [Nombre], 
    [Extent2].[Carrera] AS [Carrera], 
    [Extent3].[RecursoId] AS [RecursoId], 
    [Extent3].[Descripcion] AS [Descripcion]
    FROM   [dbo].[Asignaciones] AS [Extent1]
    INNER JOIN [dbo].[Materias] AS [Extent2] ON [Extent1].[Materia_MateriaId] = [Extent2].[MateriaId]
    INNER JOIN [dbo].[Recursos] AS [Extent3] ON [Extent1].[Recurso_RecursoId] = [Extent3].[RecursoId]
    WHERE [Extent1].[Profesor_ProfesorId] = @p0
)  AS [Project1]
ORDER BY [Project1].[FechaHoraReserva] ASC
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

Investigating a bit I think you have to see the interface IOrderedQueryable but I can not understand it.

Anyway, is it possible with Linq to generate the following query?:

SELECT 
[Extent1].[AsignacionId] AS [AsignacionId], 
[Extent1].[FechaHoraReserva] AS [FechaHoraReserva], 
[Extent2].[MateriaId] AS [MateriaId], 
[Extent2].[Nombre] AS [Nombre], 
[Extent2].[Carrera] AS [Carrera], 
[Extent3].[RecursoId] AS [RecursoId], 
[Extent3].[Descripcion] AS [Descripcion]
FROM   [dbo].[Asignaciones] AS [Extent1]
INNER JOIN [dbo].[Materias] AS [Extent2] ON [Extent1].[Materia_MateriaId] = [Extent2].[MateriaId]
INNER JOIN [dbo].[Recursos] AS [Extent3] ON [Extent1].[Recurso_RecursoId] = [Extent3].[RecursoId]
WHERE [Extent1].[Profesor_ProfesorId] = @p0
ORDER BY [Extent1].[FechaHoraReserva] ASC
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

Greetings.