Sort problem with eager loading and skip and take
Posted: Fri 15 Apr 2011 11:42
We got an entity called Relation which has a collection of Address. In our web page we want to show the data of the first Address of a Relation. Because we don't want 1+n queries, therefor we use eager loading.
Our query in EF4 is as follows:
entities.Relations.Include("Addresses").OrderBy(b => b.Name).Skip(50).Take(50);
The query created by DevArt incorrectly first skips and takes the Relations, then joins that result with the Address table and then Sorts that result by the Relation.Name. This is incorrect, because it should first sort the relations by name, then skip 50 and take the next 50 and then join the Address table.
The query generated by Devart is as follows:
If we use SQLServer as our database and therefor the standard Linq to Entities generator the following query is created:
Is there a way to tell the Devart postgresql code generator to not put the sort statement on the outside statement?
Our query in EF4 is as follows:
entities.Relations.Include("Addresses").OrderBy(b => b.Name).Skip(50).Take(50);
The query created by DevArt incorrectly first skips and takes the Relations, then joins that result with the Address table and then Sorts that result by the Relation.Name. This is incorrect, because it should first sort the relations by name, then skip 50 and take the next 50 and then join the Address table.
The query generated by Devart is as follows:
Code: Select all
SELECT
"Project2"."Id" AS "Id",
"Project2"."C1" AS "C1",
"Project2"."Name" AS "Name",
"Project2"."Created" AS "Created",
"Project2"."LastModified" AS "LastModified",
"Project2"."Blocked" AS "Blocked",
"Project2"."Deleted" AS "Deleted",
"Project2"."CreatedBy_Id" AS "CreatedBy_Id",
"Project2"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
"Project2"."C2" AS "C2",
"Project2"."Id1" AS "Id1",
"Project2"."Street" AS "Street",
"Project2"."PostalCode" AS "PostalCode",
"Project2"."City" AS "City",
"Project2"."Created1" AS "Created1",
"Project2"."LastModified1" AS "LastModified1",
"Project2"."RelationId" AS "RelationId",
"Project2"."CreatedBy_Id1" AS "CreatedBy_Id1",
"Project2"."LastModifiedBy_Id1" AS "LastModifiedBy_Id1"
FROM ( SELECT
"Limit1"."Id" AS "Id",
"Limit1"."Name" AS "Name",
"Limit1"."Created" AS "Created",
"Limit1"."LastModified" AS "LastModified",
"Limit1"."Blocked" AS "Blocked",
"Limit1"."Deleted" AS "Deleted",
"Limit1"."CreatedBy_Id" AS "CreatedBy_Id",
"Limit1"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
"Limit1"."C1" AS "C1",
"Extent2"."Id" AS "Id1",
"Extent2"."Street" AS "Street",
"Extent2"."PostalCode" AS "PostalCode",
"Extent2"."City" AS "City",
"Extent2"."Created" AS "Created1",
"Extent2"."LastModified" AS "LastModified1",
"Extent2"."RelationId" AS "RelationId",
"Extent2"."CreatedBy_Id" AS "CreatedBy_Id1",
"Extent2"."LastModifiedBy_Id" AS "LastModifiedBy_Id1",
CASE WHEN "Extent2"."Id" IS NULL THEN CAST(NULL AS int) ELSE 1 END AS "C2"
FROM (SELECT "Project1"."Id" AS "Id", "Project1"."Name" AS "Name", "Project1"."Created" AS "Created", "Project1"."LastModified" AS "LastModified", "Project1"."Blocked" AS "Blocked", "Project1"."Deleted" AS "Deleted", "Project1"."CreatedBy_Id" AS "CreatedBy_Id", "Project1"."LastModifiedBy_Id" AS "LastModifiedBy_Id", "Project1"."C1" AS "C1"
FROM ( SELECT
"Extent1"."Id" AS "Id",
"Extent1"."Name" AS "Name",
"Extent1"."Created" AS "Created",
"Extent1"."LastModified" AS "LastModified",
"Extent1"."Blocked" AS "Blocked",
"Extent1"."Deleted" AS "Deleted",
"Extent1"."CreatedBy_Id" AS "CreatedBy_Id",
"Extent1"."LastModifiedBy_Id" AS "LastModifiedBy_Id",
1 AS "C1"
FROM public."Relations" AS "Extent1"
) AS "Project1"
LIMIT 50 OFFSET 50 ) AS "Limit1"
LEFT OUTER JOIN public."Addresses" AS "Extent2" ON "Limit1"."Id" = "Extent2"."RelationId"
) AS "Project2"
ORDER BY "Project2"."Name" ASC, "Project2"."Id" ASC, "Project2"."C2" ASC
Code: Select all
SELECT
[Project2].[Id] AS [Id],
[Project2].[C1] AS [C1],
[Project2].[Name] AS [Name],
[Project2].[Created] AS [Created],
[Project2].[LastModified] AS [LastModified],
[Project2].[Blocked] AS [Blocked],
[Project2].[Deleted] AS [Deleted],
[Project2].[CreatedBy_Id] AS [CreatedBy_Id],
[Project2].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
[Project2].[C2] AS [C2],
[Project2].[Id1] AS [Id1],
[Project2].[Street] AS [Street],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[City] AS [City],
[Project2].[Created1] AS [Created1],
[Project2].[LastModified1] AS [LastModified1],
[Project2].[RelationId] AS [RelationId],
[Project2].[CreatedBy_Id1] AS [CreatedBy_Id1],
[Project2].[LastModifiedBy_Id1] AS [LastModifiedBy_Id1]
FROM ( SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Name] AS [Name],
[Limit1].[Created] AS [Created],
[Limit1].[LastModified] AS [LastModified],
[Limit1].[Blocked] AS [Blocked],
[Limit1].[Deleted] AS [Deleted],
[Limit1].[CreatedBy_Id] AS [CreatedBy_Id],
[Limit1].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
[Limit1].[C1] AS [C1],
[Extent2].[Id] AS [Id1],
[Extent2].[Street] AS [Street],
[Extent2].[PostalCode] AS [PostalCode],
[Extent2].[City] AS [City],
[Extent2].[Created] AS [Created1],
[Extent2].[LastModified] AS [LastModified1],
[Extent2].[RelationId] AS [RelationId],
[Extent2].[CreatedBy_Id] AS [CreatedBy_Id1],
[Extent2].[LastModifiedBy_Id] AS [LastModifiedBy_Id1],
CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM (SELECT TOP (10) [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[Created] AS [Created], [Project1].[LastModified] AS [LastModified], [Project1].[Blocked] AS [Blocked], [Project1].[Deleted] AS [Deleted], [Project1].[CreatedBy_Id] AS [CreatedBy_Id], [Project1].[LastModifiedBy_Id] AS [LastModifiedBy_Id], [Project1].[C1] AS [C1]
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[Created] AS [Created], [Project1].[LastModified] AS [LastModified], [Project1].[Blocked] AS [Blocked], [Project1].[Deleted] AS [Deleted], [Project1].[CreatedBy_Id] AS [CreatedBy_Id], [Project1].[LastModifiedBy_Id] AS [LastModifiedBy_Id], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[Name] ASC, [Project1].[Id] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[Created] AS [Created],
[Extent1].[LastModified] AS [LastModified],
[Extent1].[Blocked] AS [Blocked],
[Extent1].[Deleted] AS [Deleted],
[Extent1].[CreatedBy_Id] AS [CreatedBy_Id],
[Extent1].[LastModifiedBy_Id] AS [LastModifiedBy_Id],
1 AS [C1]
FROM [dbo].[Relations] AS [Extent1]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 10
ORDER BY [Project1].[Name] ASC, [Project1].[Id] ASC ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Addresses] AS [Extent2] ON [Limit1].[Id] = [Extent2].[RelationId]
) AS [Project2]
ORDER BY [Project2].[Name] ASC, [Project2].[Id] ASC, [Project2].[C2] ASC