[Solved] OrderBy LINQ to SQL bug

[Matej]

Hello, I've attempted the following (edited for brevity):

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

[Viktor Zhivkov]

Hello Matej,

Thank you for reporting this issue. I am sorry for the inconvenience that it has caused to you.
We will investigate it and if we can we will implement a fix. Meanwhile you can use your fix as work-around. It should not introduce any significant performance penalty unless you are doing paging based on the sort order.
As a small token of recognition fore reporting this new issue I have updated your Telerik points.

Regards,
Viktor Zhivkov
Telerik