Cannot use "any" in Linq-to-SQL

[markusvt]

I am using the following expression to load Data from a SQL Server:

return Mapper.Query
            .Include(v => v.Contracts).ThenInclude(c => c.Customer)
            .Where(v => !v.Temporary && v.Contracts.Any(c => c.Date_Sold.HasValue));

That throws an Error which says, that there is a syntax error near the "ORDER" Clause in SQL (a non-boolean expression was found). When I explore the SQL of the query, the Where Statement is tranlated into the following:

WHERE (([v].[Temporary] = 0) AND (
    SELECT CASE
        WHEN EXISTS (
            SELECT 1
            FROM [Caravan_Contract] AS [c]
            WHERE [c].[Date_Sold] IS NOT NULL)
        THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
    END
)
ORDER BY [v].[Id]

If i exchange the "any" Statement in LINQ with "Count(..) > 0" gets translated into the following and it works.

WHERE ([v].[Temporary] = 0) AND ((
    SELECT COUNT(*)
    FROM [Caravan_Contract] AS [c]
    WHERE [c].[Date_Sold] IS NOT NULL)
) > 0)

I am using the RC1 Codebase by the way.

[MrMiracle]

It seems to work as expected if the Any clause is the only one in the Where, but as soon as you combine it with another bool condition, it seems to drop off the check for 1/0 in the final SQL statement. Not sure if that helps

[GSPP]

SQL Server has some optimizer weaknesses around COUNT > 0 and CASE WHEN EXISTS(...) THEN 1 ELSE 0 END > 0. This forces, unfortunately, execution literally as written (a loop join).

Ideally, Entity Framework would not let such a patterns arise. Instead, it always should be plain EXISTS.

[smitpatel]

It does happen in RC1 release.
I tried reproducing this in latest bits
In latest dev, for query code like this

using (var context = CreateContext())
{
    var query = context.Customers
        .Include(c => c.Orders)
        .Where(c => c.City == "London" && c.Orders.Any(o => o.OrderID == 1))
        .ToList();
}

Generated SQL is

SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE ([c].[City] = N'London') AND EXISTS (
    SELECT 1
    FROM [Orders] AS [o]
    WHERE ([o].[OrderID] = 1) AND ([c].[CustomerID] = [o].[CustomerID]))
ORDER BY [c].[CustomerID]

It looks like it has been fixed in some other changes. It will be in RC2 release.
If you want to verify if your issue is fixed then you can try nightly builds or I can verify for you too if you can provide a standalone repro code.