This question is locked. New answers and comments are not allowed.
Hello, I've attempted the following (edited for brevity):
Note that IdForeign points to Table itself.
The generated SQL is incorrect, as it inner joins on p.PredmetnikStudijaGlavni, which is obviously not there for all rows.
We're using an Oracle database, client version 11.2.0.3.
Here's the SQL dump (also edited for brevity):
Thanks,
Matej
var stuff = context.Table
//.AsEnumerable() // hacky fix
.OrderBy(p => p.IdForeign.HasValue
? p.Foreign.AnotherTable.SortField
: p.AnotherTable.SortField)
.ThenBy(p => p.AnotherTable.SortField)
.ThenBy(p => p.IdForeign.HasValue)
.ToList();
Note that IdForeign points to Table itself.
The generated SQL is incorrect, as it inner joins on p.PredmetnikStudijaGlavni, which is obviously not there for all rows.
We're using an Oracle database, client version 11.2.0.3.
Here's the SQL dump (also edited for brevity):
SELECT a."Id" COL1,
... <various other columns> ...
,
(
CASE
WHEN
a.
"IdForeign"
IS
NOT
NULL
THEN
c.
"SortField"
ELSE
d.
"SortField"
END
) xj1,
d.
"SortField"
xj2,
(
CASE
WHEN
a.
"IdForeign"
IS
NOT
NULL
THEN
1
ELSE
0
END
) xj3
FROM
"Table"
a
JOIN
"Table"
b
-- should be FULL OUTER JOIN
ON
(a.
"IdForeign"
= b.
"Id"
)
JOIN
"AnotherTable"
c
-- should be FULL OUTER JOIN as well
ON
(b.
"IdAnother"
= c.
"Id"
)
JOIN
"AnotherTable"
d
ON
(a.
"IdAnother"
= d.
"Id"
)
ORDER
BY
xj1,
xj2,
xj3,
COL1
;
Thanks,
Matej