unexpected sorting with one to many include usage
Posted: Thu 10 Sep 2015 11:21
Im using latest oracle dotconnect version and ef 5.0 codefirst.
This code
generate this query
with strange "order by" clause
This issue broke a normal sorting.
But without include
works well
This code
Code: Select all
var query = query
.Include(p => p.Blank.Document)//1:1
.Include(p => p.IssueRequest.BlankUsers)//1:many
.Include(p => p.CurrentRequest);
Code: Select all
SELECT
"Project1"."Type",
"Project1"."RomanNumeral",
"Project1"."Region",
"Project1"."Number",
"Project1"."IssueRequestId",
"Project1"."Type1",
"Project1"."RomanNumeral1",
"Project1"."Region1",
"Project1"."Number1",
"Project1"."Status",
"Project1"."CurrentRequestId",
"Project1"."Status1",
"Project1"."DocumentId",
"Project1"."Sign",
"Project1"."ActId",
"Project1"."Processing",
"Project1"."UpdaterId",
"Project1"."Updated",
"Project1"."Id",
"Project1"."Type2",
"Project1"."Number2",
"Project1"."Caption",
"Project1"."Extension",
"Project1"."DateTime",
"Project1"."ContentId",
"Project1"."Id1",
"Project1"."Type3",
"Project1"."Status2",
"Project1"."IssueId",
"Project1"."CreatorId",
"Project1"."Created",
"Project1"."UpdaterId1",
"Project1"."Updated1",
"Project1"."Id2",
"Project1"."Type4",
"Project1"."Status3",
"Project1"."IssueId1",
"Project1"."CreatorId1",
"Project1"."Created1",
"Project1"."UpdaterId2",
"Project1"."Updated2",
"Project1".C1,
"Project1"."RequestId",
"Project1"."UserId"
FROM ( SELECT
"Extent1"."Type",
"Extent1"."RomanNumeral",
"Extent1"."Region",
"Extent1"."Number",
"Extent1"."IssueRequestId",
"Extent1"."Status",
"Extent1"."CurrentRequestId",
"Extent2"."Type" AS "Type1",
"Extent2"."RomanNumeral" AS "RomanNumeral1",
"Extent2"."Region" AS "Region1",
"Extent2"."Number" AS "Number1",
"Extent2"."Status" AS "Status1",
"Extent2"."DocumentId",
"Extent2"."Sign",
"Extent2"."ActId",
"Extent2"."Processing",
"Extent2"."UpdaterId",
"Extent2"."Updated",
"Extent3"."Id",
"Extent3"."Type" AS "Type2",
"Extent3"."Number" AS "Number2",
"Extent3"."Caption",
"Extent3"."Extension",
"Extent3"."DateTime",
"Extent3"."ContentId",
"Extent4"."Id" AS "Id1",
"Extent4"."Type" AS "Type3",
"Extent4"."Status" AS "Status2",
"Extent4"."IssueId",
"Extent4"."CreatorId",
"Extent4"."Created",
"Extent4"."UpdaterId" AS "UpdaterId1",
"Extent4"."Updated" AS "Updated1",
"Extent5"."Id" AS "Id2",
"Extent5"."Type" AS "Type4",
"Extent5"."Status" AS "Status3",
"Extent5"."IssueId" AS "IssueId1",
"Extent5"."CreatorId" AS "CreatorId1",
"Extent5"."Created" AS "Created1",
"Extent5"."UpdaterId" AS "UpdaterId2",
"Extent5"."Updated" AS "Updated2",
"Extent6"."RequestId",
"Extent6"."UserId",
CASE WHEN "Extent6"."RequestId" IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1
FROM "BlankIssued" "Extent1"
INNER JOIN "Blank" "Extent2" ON ((("Extent1"."Number" = "Extent2"."Number") AND ("Extent1"."Region" = "Extent2"."Region")) AND ("Extent1"."RomanNumeral" = "Extent2"."RomanNumeral")) AND ("Extent1"."Type" = "Extent2"."Type")
LEFT OUTER JOIN "Document" "Extent3" ON "Extent2"."DocumentId" = "Extent3"."Id"
INNER JOIN "Request" "Extent4" ON "Extent1"."IssueRequestId" = "Extent4"."Id"
LEFT OUTER JOIN "Request" "Extent5" ON "Extent1"."CurrentRequestId" = "Extent5"."Id"
LEFT OUTER JOIN "BlankUser" "Extent6" ON "Extent6"."RequestId" = "Extent1"."IssueRequestId"
) "Project1"
ORDER BY "Project1"."Type" ASC, "Project1"."RomanNumeral" ASC, "Project1"."Region" ASC, "Project1"."Number" ASC, "Project1"."IssueRequestId" ASC, "Project1"."Type1" ASC, "Project1"."RomanNumeral1" ASC, "Project1"."Region1" ASC, "Project1"."Number1" ASC, "Project1"."Id" ASC, "Project1"."Id1" ASC, "Project1"."Id2" ASC, "Project1".C1 ASC
Code: Select all
ORDER BY "Project1"."Type" ASC, "Project1"."RomanNumeral" ASC, "Project1"."Region" ASC, "Project1"."Number" ASC, "Project1"."IssueRequestId" ASC, "Project1"."Type1" ASC, "Project1"."RomanNumeral1" ASC, "Project1"."Region1" ASC, "Project1"."Number1" ASC, "Project1"."Id" ASC, "Project1"."Id1" ASC, "Project1"."Id2" ASC, "Project1".C1 ASC
But without include
Code: Select all
var query = query
.Include(p => p.Blank.Document)
.Include(p => p.IssueRequest)
.Include(p => p.CurrentRequest);
Code: Select all
SELECT
"Extent1"."Number",
"Extent1"."Type",
"Extent1"."RomanNumeral",
"Extent1"."Region",
"Extent1"."IssueRequestId",
"Extent1"."Status",
"Extent1"."CurrentRequestId",
"Extent2"."Type" AS "Type1",
"Extent2"."RomanNumeral" AS "RomanNumeral1",
"Extent2"."Region" AS "Region1",
"Extent2"."Number" AS "Number1",
"Extent2"."Status" AS "Status1",
"Extent2"."DocumentId",
"Extent2"."Sign",
"Extent2"."ActId",
"Extent2"."Processing",
"Extent2"."UpdaterId",
"Extent2"."Updated",
"Extent3"."Id",
"Extent3"."Type" AS "Type2",
"Extent3"."Number" AS "Number2",
"Extent3"."Caption",
"Extent3"."Extension",
"Extent3"."DateTime",
"Extent3"."ContentId",
"Extent4"."Id" AS "Id1",
"Extent4"."Type" AS "Type3",
"Extent4"."Status" AS "Status2",
"Extent4"."IssueId",
"Extent4"."CreatorId",
"Extent4"."Created",
"Extent4"."UpdaterId" AS "UpdaterId1",
"Extent4"."Updated" AS "Updated1",
"Extent5"."Id" AS "Id2",
"Extent5"."Type" AS "Type4",
"Extent5"."Status" AS "Status3",
"Extent5"."IssueId" AS "IssueId1",
"Extent5"."CreatorId" AS "CreatorId1",
"Extent5"."Created" AS "Created1",
"Extent5"."UpdaterId" AS "UpdaterId2",
"Extent5"."Updated" AS "Updated2"
FROM "BlankIssued" "Extent1"
INNER JOIN "Blank" "Extent2" ON ((("Extent1"."Number" = "Extent2"."Number") AND ("Extent1"."Region" = "Extent2"."Region")) AND ("Extent1"."RomanNumeral" = "Extent2"."RomanNumeral")) AND ("Extent1"."Type" = "Extent2"."Type")
LEFT OUTER JOIN "Document" "Extent3" ON "Extent2"."DocumentId" = "Extent3"."Id"
INNER JOIN "Request" "Extent4" ON "Extent1"."IssueRequestId" = "Extent4"."Id"
LEFT OUTER JOIN "Request" "Extent5" ON "Extent1"."CurrentRequestId" = "Extent5"."Id"