N\u00e9ha szeretn\u00e9nk nem csak egy entit\u00e1s list\u00e1t, hanem annak gyerekeit is rendeztetni, azaz az egy sz\u00fcl\u0151 al\u00e1 tartoz\u00f3 gyerekeket order by-olni.<\/p>\n
Egy lehets\u00e9ges megold\u00e1s:<\/p>\n
\r\nusing (var c = new EdbContext())\r\n{\r\n var jobs = c.LoaderJobs\r\n .Include("LoaderJobSteps")\r\n .Include("LoaderJobSteps.ExtractPathLocation")\r\n .Include("LoaderJobSteps.FormatFilePathLocation")\r\n .OrderBy(j => j.ExecutionOrder).ToList();\r\n\r\n \/\/A bit complicated to be able to order LoaderJobSteps properly\r\n var x = jobs.Select(job => new { J = job, JS = job.LoaderJobSteps.OrderBy(js => js.ExecutionOrder).ToList() });\r\n\r\n return x.Select(f => f.J).ToList();\r\n}\r\n<\/pre>\nSz\u00e1nd\u00e9kaim szerint a gyerekkollekci\u00f3k order by-\u00e1t is az adatb\u00e1zissal v\u00e9geztettem volna, de j\u00f3l l\u00e1that\u00f3 a k\u00f3db\u00f3l, hogy itt .netb\u0151l t\u00f6rt\u00e9nik a gyerekek (LoaderJobSteps) rendez\u00e9se.
\nHa kiveszem az els\u0151 ToList()-et, akkor az EF helyesen \u00e1ttolja a 2. order by-t is az SQL Serverre, de akkor meg nem t\u00f6lti be az unok\u00e1kat (LoaderJobSteps.ExtractPathLocation).
\nEbben a p\u00e9ld\u00e1ban nincs jelent\u0151s\u00e9ge hol rendezek, de ha valaki tudja, mit\u0151l nem megy ilyenkor az Include, \u00e9rdekelne a megold\u00e1s.<\/p>\n
Ha benn van az els\u0151 ToList(), akkor az SQL ok, benne van minden eagerly loaded entit\u00e1s, de nincs benne order by a gyerekekre, az a LINQ2Objects fogja v\u00e9grehajtani. (Extent2-re nincs order by).<\/p>\n
\r\nSELECT \r\n [Project1].[Id] AS [Id], \r\n [Project1].[Description] AS [Description], \r\n [Project1].[ExecutionOrder] AS [ExecutionOrder], \r\n [Project1].[C1] AS [C1], \r\n [Project1].[Id1] AS [Id1], \r\n [Project1].[StepName] AS [StepName], \r\n [Project1].[ExecutionOrder1] AS [ExecutionOrder1], \r\n [Project1].[ProcedureNamePrepare] AS [ProcedureNamePrepare], \r\n [Project1].[ProcedureNameImport] AS [ProcedureNameImport], \r\n [Project1].[ProcedureNameLoad] AS [ProcedureNameLoad], \r\n [Project1].[ExtractFileName] AS [ExtractFileName], \r\n [Project1].[FailOnMissingFile] AS [FailOnMissingFile], \r\n [Project1].[FormatFileName] AS [FormatFileName], \r\n [Project1].[Id2] AS [Id2], \r\n [Project1].[FolderPath] AS [FolderPath], \r\n [Project1].[Id3] AS [Id3], \r\n [Project1].[FolderPath1] AS [FolderPath1], \r\n [Project1].[LoaderJobId] AS [LoaderJobId]\r\n FROM ( SELECT \r\n [Extent1].[Id] AS [Id], \r\n [Extent1].[Description] AS [Description], \r\n [Extent1].[ExecutionOrder] AS [ExecutionOrder], \r\n [Join2].[Id1] AS [Id1], \r\n [Join2].[StepName] AS [StepName], \r\n [Join2].[ExecutionOrder] AS [ExecutionOrder1], \r\n [Join2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], \r\n [Join2].[ProcedureNameImport] AS [ProcedureNameImport], \r\n [Join2].[ProcedureNameLoad] AS [ProcedureNameLoad], \r\n [Join2].[ExtractFileName] AS [ExtractFileName], \r\n [Join2].[FailOnMissingFile] AS [FailOnMissingFile], \r\n [Join2].[FormatFileName] AS [FormatFileName], \r\n [Join2].[LoaderJobId] AS [LoaderJobId], \r\n [Join2].[Id2] AS [Id2], \r\n [Join2].[FolderPath1] AS [FolderPath], \r\n [Join2].[Id3] AS [Id3], \r\n [Join2].[FolderPath2] AS [FolderPath1], \r\n CASE WHEN ([Join2].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]\r\n FROM [dbo].[LoaderJob] AS [Extent1]\r\n 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]\r\n FROM [dbo].[LoaderJobStep] AS [Extent2]\r\n LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent3] ON [Extent2].[ExtractPathLocationId] = [Extent3].[Id]\r\n LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent4] ON [Extent2].[FormatFilePathLocationId] = [Extent4].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[LoaderJobId]\r\n ) AS [Project1]\r\n ORDER BY [Project1].[ExecutionOrder] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC\r\n<\/pre>\nAz els\u0151 ToList() n\u00e9lk\u00fcl:<\/p>\n
\r\nSELECT \r\n [Extent1].[Id] AS [Id], \r\n [Extent1].[Description] AS [Description], \r\n [Extent1].[ExecutionOrder] AS [ExecutionOrder]\r\n FROM [dbo].[LoaderJob] AS [Extent1]\r\n ORDER BY [Extent1].[ExecutionOrder] ASC\r\n<\/pre>\nHm. Tippek? <\/p>\n
K\u00f6zben r\u00e1j\u00f6ttem, megv\u00e1laszolom magamnak a k\u00e9rd\u00e9st. :) A v\u00e9g\u00e9re kell rakni az include-okat:<\/p>\n
using (var c = new EdbContext())
\n{
\n var jobs = c.LoaderJobs
\n .OrderBy(j => j.ExecutionOrder);<\/p>\n
\/\/A bit complicated to be able to order LoaderJobSteps properly
\n var x = jobs.Select(job => new { J = job, JS = job.LoaderJobSteps.OrderBy(js => js.ExecutionOrder) });<\/p>\n
return x.Select(f => f.J).Include(“LoaderJobSteps”)
\n .Include(“LoaderJobSteps.ExtractPathLocation”)
\n .Include(“LoaderJobSteps.FormatFilePathLocation”).ToList();
\n}
\n[\/source]<\/p>\n
\r\nSELECT \r\n [Project1].[Id] AS [Id], \r\n [Project1].[Description] AS [Description], \r\n [Project1].[ExecutionOrder] AS [ExecutionOrder], \r\n [Project1].[C1] AS [C1], \r\n [Project1].[Id1] AS [Id1], \r\n [Project1].[StepName] AS [StepName], \r\n [Project1].[ExecutionOrder1] AS [ExecutionOrder1], \r\n [Project1].[ProcedureNamePrepare] AS [ProcedureNamePrepare], \r\n [Project1].[ProcedureNameImport] AS [ProcedureNameImport], \r\n [Project1].[ProcedureNameLoad] AS [ProcedureNameLoad], \r\n [Project1].[ExtractFileName] AS [ExtractFileName], \r\n [Project1].[FailOnMissingFile] AS [FailOnMissingFile], \r\n [Project1].[FormatFileName] AS [FormatFileName], \r\n [Project1].[Id2] AS [Id2], \r\n [Project1].[FolderPath] AS [FolderPath], \r\n [Project1].[Id3] AS [Id3], \r\n [Project1].[FolderPath1] AS [FolderPath1], \r\n [Project1].[LoaderJobId] AS [LoaderJobId]\r\n FROM ( SELECT \r\n [Extent1].[Id] AS [Id], \r\n [Extent1].[Description] AS [Description], \r\n [Extent1].[ExecutionOrder] AS [ExecutionOrder], \r\n [Join2].[Id1] AS [Id1], \r\n [Join2].[StepName] AS [StepName], \r\n [Join2].[ExecutionOrder] AS [ExecutionOrder1], \r\n [Join2].[ProcedureNamePrepare] AS [ProcedureNamePrepare], \r\n [Join2].[ProcedureNameImport] AS [ProcedureNameImport], \r\n [Join2].[ProcedureNameLoad] AS [ProcedureNameLoad], \r\n [Join2].[ExtractFileName] AS [ExtractFileName], \r\n [Join2].[FailOnMissingFile] AS [FailOnMissingFile], \r\n [Join2].[FormatFileName] AS [FormatFileName], \r\n [Join2].[LoaderJobId] AS [LoaderJobId], \r\n [Join2].[Id2] AS [Id2], \r\n [Join2].[FolderPath1] AS [FolderPath], \r\n [Join2].[Id3] AS [Id3], \r\n [Join2].[FolderPath2] AS [FolderPath1], \r\n CASE WHEN ([Join2].[Id1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]\r\n FROM [dbo].[LoaderJob] AS [Extent1]\r\n 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]\r\n FROM [dbo].[LoaderJobStep] AS [Extent2]\r\n LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent3] ON [Extent2].[ExtractPathLocationId] = [Extent3].[Id]\r\n LEFT OUTER JOIN [dbo].[PathLocation] AS [Extent4] ON [Extent2].[FormatFilePathLocationId] = [Extent4].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[LoaderJobId]\r\n ) AS [Project1]\r\n ORDER BY [Project1].[ExecutionOrder] ASC, [Project1].[Id] ASC, [Project1].[C1] ASC\r\n<\/pre>\nHm, m\u00e9gse j\u00f3, nincs benne az sql-ben a 2. order by. Sz\u00f3val a k\u00e9rd\u00e9s nyitott, \u00e1t lehet tolni adatb\u00e1zis oldalra a child kollekci\u00f3 order by-t?<\/p>\n","protected":false},"excerpt":{"rendered":"
N\u00e9ha szeretn\u00e9nk nem csak egy entit\u00e1s list\u00e1t, hanem annak gyerekeit is rendeztetni, azaz az egy sz\u00fcl\u0151 al\u00e1 tartoz\u00f3 gyerekeket order by-olni. Egy…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,10,6,74,4,30],"tags":[],"class_list":["post-1998","post","type-post","status-publish","format-standard","hentry","category-net","category-c","category-adatbazisok","category-entity-framework","category-szakmai-elet","category-sql-server"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1998"}],"version-history":[{"count":2,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1998\/revisions"}],"predecessor-version":[{"id":2000,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1998\/revisions\/2000"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}