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