unexpected sorting with one to many include usage

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Sv01a
Posts: 1
Joined: Mon 12 Jan 2015 13:01

unexpected sorting with one to many include usage

Post by Sv01a » Thu 10 Sep 2015 11:21

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"

Shalex
Site Admin
Posts: 8369
Joined: Thu 14 Aug 2008 12:44

Re: unexpected sorting with one to many include usage

Post by Shalex » Mon 14 Sep 2015 12:59

Please send us a small complete test project with the corresponding DDL/DML script.

chrsas
Posts: 1
Joined: Sat 09 Jul 2016 05:02

Re: unexpected sorting with one to many include usage

Post by chrsas » Sat 09 Jul 2016 06:51

I have the same problem,is it solved?

Shalex
Site Admin
Posts: 8369
Joined: Thu 14 Aug 2008 12:44

Re: unexpected sorting with one to many include usage

Post by Shalex » Mon 11 Jul 2016 09:27

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.

Post Reply