Entity Framework generated really bad SQL when using ANY

Question

I'm analyzing the queries that are running in a DB (Azure Sql Server V12) and I found a few queries generated by Entity Framework (EF 6.0) that make no sense to me. They are really bad for performance and I cannot find where they are generated.

(@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 a query like this it means 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.

Just wondering - you really expect we would guess what kind of code generated this SQL without looking at type definitions and LINQ queries you have? — Sergey Berezovskiy
– Sergey Berezovskiy, Commented Apr 28, 2017 at 15:40
No, I'm not asking for a guess, maybe someone else faced the same issue. I have found 3 queries like this one and couldn't find them by filtering by those properties. — Francisco Goldenstein
– Francisco Goldenstein, Commented Apr 28, 2017 at 15:54

Tipx · Accepted Answer · 2017-04-28 16:30:36Z

1

To find out where the query is generated, enable the EF Logging (either to the console or your logging framework.) Once you have that turned on, try to find part of the query (like [dbo].[SellerPhone] AS [Extent2]) in your logs, and with the other logs surrounding the query, you should know where you are.

This should help you enable the logging:

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

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

answered Apr 28, 2017 at 16:30

Tipx

7,5354 gold badges40 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Francisco Goldenstein Over a year ago

The log will tell you the exact point where the EF query was built? have you tried it in PROD? I'm worried about the performance impact it might have.

Tipx Over a year ago

The log will tell you what the query is, when it is constructed. Depending on how you log stuff, you might have to correlate things from there. Your performance concern is relevant, so I suggest you try it in dev first (I assumed you were in dev.) What I actually do is my constructor takes an argument whether or not it should log, and based on the configuration file, UnityIoC creates the context with or without logs.

Francisco Goldenstein Over a year ago

Thanks! I'm gonna try it. I upvoted your answer and I'll probably mark it as the correct answer when this is implemented.

Francisco Goldenstein · Accepted Answer · 2017-05-04 23:03:31Z

I did what Tipx described but I also added the stack trace to be able to know which method was generating each query:

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

In EF 6.0, a simple query like dbContext.SellerPhones.Any(p => condition) will generate an ugly query that has a CASE and calls the EXISTS twice.

After upgrading to EF 6.1 the query has only ONE EXISTS and performs much better (I highlighted below that part of the generated query that was updated).

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

Collectives™ on Stack Overflow

Entity Framework generated really bad SQL when using ANY

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related