Entity Framework generates very bad SQL when using ANY

Question

I am analyzing queries running in a database (Azure Sql Server V12), and I have found several queries generated by Entity Framework (EF 6.0) that do not make any sense to me. They are very poor for performance, and I cannot find where they are created.

(@p__linq__0 nvarchar(4000))SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[SellerPhone] AS [Extent1]
        WHERE [Extent1].[Phone] = @p__linq__0
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[SellerPhone] AS [Extent2]
        WHERE [Extent2].[Phone] = @p__linq__0
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Solution: if you have such a request, it means that you have EF 6.0 or older and you are doing a simple dbContext.SellerPhones.Any (p => xxx). Just upgrade to 6.1 and the generated query will be much better.

Answer 1

, Tipx, , , :

Database.Log = (sql => 
            {
                System.Diagnostics.Debug.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
                System.Diagnostics.Debug.WriteLine(sql);
            });

EF 6.0 , dbContext.SellerPhones.Any(p = > condition), CASE EXISTS.

EF 6.1 EXISTS ( , ).

SELECT   CASE WHEN (EXISTS (SELECT)       1 AS [C1]       FROM [dbo]. [Phone] AS [Extent1]       WHERE [Extent1]. [] = @p__linq__0   )) THEN cast (1 ) ELSE cast (0 ) END AS [C1]   FROM (SELECT 1 AS X) AS [SingleRowTable1]

Answer 2

, , EF ( , .) , , (, [dbo].[SellerPhone] AS [Extent2]) , , , , .

:

public MyContext : DbContext
{
    private static ILog log = LogManager.GetCurrentClassLogger();

    public MyContext(string connectionString)
        : base(connectionString)
    {
        this.Database.Log = (msg) => log.Trace(msg);
    }
}