Efcore: Query with nested OrderBy bool predicate throws SqlException

[mikary]

The following query throws a SqlException on SQL Server with the message: Incorrect syntax near '<'

        [ConditionalFact]
        public virtual void OtherTest()
        {
            AssertQuery<Customer, Order>(
                (cs, os) => cs.Any(c =>
                    c.CustomerID == os.OrderBy(o => o.CustomerID != "ALFKI").First().CustomerID));
        }

The generated SQL looks like:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Customers] AS [c]
        WHERE [c].[CustomerID] = (
            SELECT TOP(1) [o].[CustomerID]
            FROM [Orders] AS [o]
            ORDER BY [o].[CustomerID] <> 'ALFKI'
        ))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

[GZidar]

I have a similar issue but it generates a different error. In my case the SQL exception is: "An expression of non-boolean type specified in a context where a condition is expected"

when running this query in code:

DB.Accounts.Any(a => a.Status != AccountStatus.Deleted && a.Contacts.Any(c => c.EMailAddress == model.EmailAddress))

which generates the following SQL

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Account] AS [a]
        WHERE ([a].[Status] <> 2) AND (
            SELECT CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM [ContactInfo] AS [c]
                    WHERE ([c].[EMailAddress] = '[email protected]') AND ([a].[Id] = [c].[AccountId]))
                THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
            END
        ))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)

The problem appears to be that bit by itself is not Boolean and requires a comparison to work. By adding = 1 to the inner query I can execute it without error.

[divega]

The problem appears to be that bit by itself is not Boolean and requires a comparison to work

These are kind of opposite problems within the same overarching issue:

In memory Boolean expressions are uniform. Any place where a Boolean expression is accepted can either take a comparison or just a value stored in a variable. But in SQL Server (and other databases) search conditions and BIT values are two different things. So when we find Boolean expressions in a LINQ query we sometimes need to translate them into a search expression and sometimes into a BIT value.

E.g. inside a WHERE clause you can do a comparison directly but in the SELECT or the ORDER BY clause if you find a comparison expression you need to wrap it into CASE WHEN exp THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)).

Conversely if you have a Boolean expression that is just a BIT value (e.g. a column, parameter or the result of a function call) in the WHERE clause we need to wrap it into (exp = 1) but in the SELECT or ORDER clauses it is ok to use the value directly.

[Tasteful]

As workaround you can probably use .Count() > 0 instead of .Any() untill the bug is fixed.

[mikary]

Original query does not throw the exception on current dev.

Generated SQL is now (OrderBy now contains Case statement):

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Customers] AS [c]
        WHERE [c].[CustomerID] = (
            SELECT TOP(1) [o].[CustomerID]
            FROM [Orders] AS [o]
            ORDER BY CASE
                WHEN [o].[CustomerID] <> N'ALFKI'
                THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
            END
        ))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

[mikary]

Attempted to create a similar query to the one provided by @GZidar

Query code:

context.Customers.Any(c =>
    c.City != "Seattle" && c.Orders.Any(o => o.OrderDate == DateTime.Now));

Generated SQL:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Customers] AS [c]
        WHERE (([c].[City] <> N'Seattle') OR [c].[City] IS NULL) AND EXISTS (
            SELECT 1
            FROM [Orders] AS [o]
            WHERE ([o].[OrderDate] = GETDATE()) AND ([c].[CustomerID] = [o].[CustomerID])))
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END

This query completes successfully. Note the inner Case statement is not present in this query.

[smitpatel]

Order By issue fixed in #5122
Any issue is fixed during some changes after RC1, tests are added in #5006