Page 1 of 1

unexpected sorting with one to many include usage

Posted: Thu 10 Sep 2015 11:21
by Sv01a
Im using latest oracle dotconnect version and ef 5.0 codefirst.
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);
generate this query

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
with strange "order by" clause

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
This issue broke a normal sorting.

But without include

Code: Select all

var query = query
                .Include(p => p.Blank.Document)
                .Include(p => p.IssueRequest)
                .Include(p => p.CurrentRequest);
works well

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"

Re: unexpected sorting with one to many include usage

Posted: Mon 14 Sep 2015 12:59
by Shalex
Please send us a small complete test project with the corresponding DDL/DML script.

Re: unexpected sorting with one to many include usage

Posted: Sat 09 Jul 2016 06:51
by chrsas
I have the same problem´╝îis it solved?

Re: unexpected sorting with one to many include usage

Posted: Mon 11 Jul 2016 09:27
by Shalex
chrsas wrote:I have the same problem´╝îis it solved?
Please send us a small complete test project with the corresponding DDL/DML script so that we can reproduce and investigate the issue in our environment.