Devart Forums

Posted: **Mon 15 Jun 2020 08:01**

I want to Order my collection by comparing a value to null. That way I can force nulls to appear first or last for specific query. The problem is that Devart.Data.Oracle.EFCore (version 9.11.980) generates invalid SQL query.

Example LINQ query:

Code: Select all

context.Entities
    .OrderBy(x => x.SomeString != null)
    .ThenBy(x => x.SomeString)
    .ToList()

Generated SQL (not valid):

Code: Select all

SELECT "v".SOME_STRING
FROM ENTITY "v"
ORDER BY "v".SOME_STRING IS NOT NULL, "v".SOME_STRING

I would expect it to generate something like:

Code: Select all

SELECT "v".SOME_STRING
FROM ENTITY "v"
ORDER BY CASE WHEN "v".SOME_STRING IS NOT NULL THEN 1 ELSE 0 END, "v".SOME_STRING

I think the context I have provided should be enough but let me know if you need any additional details.

Posted: **Sat 04 Jul 2020 16:47**

Thank you for your report. We have reproduced the bug and will notify you when it is fixed.

Posted: **Fri 17 Jul 2020 08:36**

The bug with incorrect SQL generation when using .OrderBy() with comparison in EF Core is fixed: viewtopic.php?f=1&t=41392.

Devart Forums

Invalid SQL when Ordering by comparison with null

Invalid SQL when Ordering by comparison with null

Re: Invalid SQL when Ordering by comparison with null

Re: Invalid SQL when Ordering by comparison with null