Soci (Soczó Zsolt) szakmai blogja

2015.03.18.

EF gyerek kollekció rendezés

Filed under: .NET,Adatbázisok,C#,Entity Framework,SQL Server,Szakmai élet — Soczó Zsolt @ 18:03

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:

[source lang=”csharp”]
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();
}
[/source]

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).

[source lang=”sql”]
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
[/source]

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

[source lang=”sql”]
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Description] AS [Description],
[Extent1].[ExecutionOrder] AS [ExecutionOrder]
FROM [dbo].[LoaderJob] AS [Extent1]
ORDER BY [Extent1].[ExecutionOrder] ASC
[/source]

Hm. Tippek?

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

[source lang=”csharp”][/source]
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]

[source lang=”sql”]
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
[/source]

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?

1 Comment

  1. Akkor mi történik, ha a child lesz a parent? A lekérdezést a LoaderJobSteps.ExtractPathLocation-ből indítod, ehhez include-olsz mindent, és ezt rendezed egy Orderby-ThenBy kombóval.

    Comment by Peter Kiss — 2015.03.19. @ 10:29

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress