Néha szeretnénk nem csak egy entitás listát, hanem annak gyerekeit is rendeztetni, azaz az egy szülő alá tartozó gyerekeket order by-olni.

Egy lehetséges megoldás:

using (var c = new EdbContext())
{
    var jobs = c.LoaderJobs
        .Include("LoaderJobSteps")
        .Include("LoaderJobSteps.ExtractPathLocation")
        .Include("LoaderJobSteps.FormatFilePathLocation")
        .OrderBy(j => j.ExecutionOrder).ToList();

    //A bit complicated to be able to order LoaderJobSteps properly
    var x = jobs.Select(job => new { J = job, JS = job.LoaderJobSteps.OrderBy(js => js.ExecutionOrder).ToList() });

    return x.Select(f => f.J).ToList();
}

Szándékaim szerint a gyerekkollekciók order by-át is az adatbázissal végeztettem volna, de jól látható a kódból, hogy itt .netből történik a gyerekek (LoaderJobSteps) rendezése.
Ha kiveszem az első ToList()-et, akkor az EF helyesen áttolja a 2. order by-t is az SQL Serverre, de akkor meg nem tölti be az unokákat (LoaderJobSteps.ExtractPathLocation).
Ebben a példában nincs jelentősége hol rendezek, de ha valaki tudja, mitől nem megy ilyenkor az Include, érdekelne a megoldás.

Ha benn van az első ToList(), akkor az SQL ok, benne van minden eagerly loaded entitás, de nincs benne order by a gyerekekre, az a LINQ2Objects fogja végrehajtani. (Extent2-re nincs order by).

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Description] AS [Description], 
    [Project1].[ExecutionOrder] AS [ExecutionOrder], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[StepName] AS [StepName], 
    [Project1].[ExecutionOrder1] AS [ExecutionOrder1], 
    [Project1].[ProcedureNamePrepare] AS [ProcedureNamePrepare], 
    [Project1].[ProcedureNameImport] AS [ProcedureNameImport], 
    [Project1].[ProcedureNameLoad] AS [ProcedureNameLoad], 
    [Project1].[ExtractFileName] AS [ExtractFileName], 
    [Project1].[FailOnMissingFile] AS [FailOnMissingFile], 
    [Project1].[FormatFileName] AS [FormatFileName], 
    [Project1].[Id2] AS [Id2], 
    [Project1].[FolderPath] AS [FolderPath], 
    [Project1].[Id3] AS [Id3], 
    [Project1].[FolderPath1] AS [FolderPath1], 
    [Project1].[LoaderJobId] AS [LoaderJobId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[ExecutionOrder] AS [ExecutionOrder], 
        [Join2].[Id1] AS [Id1], 
        [Join2].[StepName] AS [StepName], 
        [Join2].[ExecutionOrder] AS [ExecutionOrder1], 
        [Join2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], 
        [Join2].[ProcedureNameImport] AS [ProcedureNameImport], 
        [Join2].[ProcedureNameLoad] AS [ProcedureNameLoad], 
        [Join2].[ExtractFileName] AS [ExtractFileName], 
        [Join2].[FailOnMissingFile] AS [FailOnMissingFile], 
        [Join2].[FormatFileName] AS [FormatFileName], 
        [Join2].[LoaderJobId] AS [LoaderJobId], 
        [Join2].[Id2] AS [Id2], 
        [Join2].[FolderPath1] AS [FolderPath], 
        [Join2].[Id3] AS [Id3], 
        [Join2].[FolderPath2] AS [FolderPath1], 
        CASE WHEN ([Join2].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[LoaderJob] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[Id] AS [Id1], [Extent2].[StepName] AS [StepName], [Extent2].[ExecutionOrder] AS [ExecutionOrder], [Extent2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], [Extent2].[ProcedureNameImport] AS [ProcedureNameImport], [Extent2].[ProcedureNameLoad] AS [ProcedureNameLoad], [Extent2].[ExtractFileName] AS [ExtractFileName], [Extent2].[FailOnMissingFile] AS [FailOnMissingFile], [Extent2].[FormatFileName] AS [FormatFileName], [Extent2].[LoaderJobId] AS [LoaderJobId], [Extent3].[Id] AS [Id2], [Extent3].[FolderPath] AS [FolderPath1], [Extent4].[Id] AS [Id3], [Extent4].[FolderPath] AS [FolderPath2]
            FROM   [dbo].[LoaderJobStep] AS [Extent2]
            LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent3] ON [Extent2].[ExtractPathLocationId] = [Extent3].[Id]
            LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent4] ON [Extent2].[FormatFilePathLocationId] = [Extent4].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[LoaderJobId]
    )  AS [Project1]
    ORDER BY [Project1].[ExecutionOrder] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC

Az első ToList() nélkül:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[ExecutionOrder] AS [ExecutionOrder]
    FROM [dbo].[LoaderJob] AS [Extent1]
    ORDER BY [Extent1].[ExecutionOrder] ASC

Hm. Tippek?

Közben rájöttem, megválaszolom magamnak a kérdést. :) A végére kell rakni az include-okat:

using (var c = new EdbContext())
{
var jobs = c.LoaderJobs
.OrderBy(j => j.ExecutionOrder);

//A bit complicated to be able to order LoaderJobSteps properly
var x = jobs.Select(job => new { J = job, JS = job.LoaderJobSteps.OrderBy(js => js.ExecutionOrder) });

return x.Select(f => f.J).Include(“LoaderJobSteps”)
.Include(“LoaderJobSteps.ExtractPathLocation”)
.Include(“LoaderJobSteps.FormatFilePathLocation”).ToList();
}
[/source]

SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[Description] AS [Description], 
    [Project1].[ExecutionOrder] AS [ExecutionOrder], 
    [Project1].[C1] AS [C1], 
    [Project1].[Id1] AS [Id1], 
    [Project1].[StepName] AS [StepName], 
    [Project1].[ExecutionOrder1] AS [ExecutionOrder1], 
    [Project1].[ProcedureNamePrepare] AS [ProcedureNamePrepare], 
    [Project1].[ProcedureNameImport] AS [ProcedureNameImport], 
    [Project1].[ProcedureNameLoad] AS [ProcedureNameLoad], 
    [Project1].[ExtractFileName] AS [ExtractFileName], 
    [Project1].[FailOnMissingFile] AS [FailOnMissingFile], 
    [Project1].[FormatFileName] AS [FormatFileName], 
    [Project1].[Id2] AS [Id2], 
    [Project1].[FolderPath] AS [FolderPath], 
    [Project1].[Id3] AS [Id3], 
    [Project1].[FolderPath1] AS [FolderPath1], 
    [Project1].[LoaderJobId] AS [LoaderJobId]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[ExecutionOrder] AS [ExecutionOrder], 
        [Join2].[Id1] AS [Id1], 
        [Join2].[StepName] AS [StepName], 
        [Join2].[ExecutionOrder] AS [ExecutionOrder1], 
        [Join2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], 
        [Join2].[ProcedureNameImport] AS [ProcedureNameImport], 
        [Join2].[ProcedureNameLoad] AS [ProcedureNameLoad], 
        [Join2].[ExtractFileName] AS [ExtractFileName], 
        [Join2].[FailOnMissingFile] AS [FailOnMissingFile], 
        [Join2].[FormatFileName] AS [FormatFileName], 
        [Join2].[LoaderJobId] AS [LoaderJobId], 
        [Join2].[Id2] AS [Id2], 
        [Join2].[FolderPath1] AS [FolderPath], 
        [Join2].[Id3] AS [Id3], 
        [Join2].[FolderPath2] AS [FolderPath1], 
        CASE WHEN ([Join2].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[LoaderJob] AS [Extent1]
        LEFT OUTER JOIN  (SELECT [Extent2].[Id] AS [Id1], [Extent2].[StepName] AS [StepName], [Extent2].[ExecutionOrder] AS [ExecutionOrder], [Extent2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], [Extent2].[ProcedureNameImport] AS [ProcedureNameImport], [Extent2].[ProcedureNameLoad] AS [ProcedureNameLoad], [Extent2].[ExtractFileName] AS [ExtractFileName], [Extent2].[FailOnMissingFile] AS [FailOnMissingFile], [Extent2].[FormatFileName] AS [FormatFileName], [Extent2].[LoaderJobId] AS [LoaderJobId], [Extent3].[Id] AS [Id2], [Extent3].[FolderPath] AS [FolderPath1], [Extent4].[Id] AS [Id3], [Extent4].[FolderPath] AS [FolderPath2]
            FROM   [dbo].[LoaderJobStep] AS [Extent2]
            LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent3] ON [Extent2].[ExtractPathLocationId] = [Extent3].[Id]
            LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent4] ON [Extent2].[FormatFilePathLocationId] = [Extent4].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[LoaderJobId]
    )  AS [Project1]
    ORDER BY [Project1].[ExecutionOrder] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC

Hm, mégse jó, nincs benne az sql-ben a 2. order by. Szóval a kérdés nyitott, át lehet tolni adatbázis oldalra a child kollekció order by-t?

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.