unexpected sorting with one to many include usage

unexpected sorting with one to many include usage

Postby 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"
Sv01a
 
Posts: 1
Joined: Mon 12 Jan 2015 13:01

Re: unexpected sorting with one to many include usage

Postby Shalex » Mon 14 Sep 2015 12:59

Please send us a small complete test project with the corresponding DDL/DML script.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Re: unexpected sorting with one to many include usage

Postby chrsas » Sat 09 Jul 2016 06:51

I have the same problem,is it solved?
chrsas
 
Posts: 1
Joined: Sat 09 Jul 2016 05:02

Re: unexpected sorting with one to many include usage

Postby 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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle