Why does Entity Framework 6 generate complex SQL queries for simple lookups?

Question

I have this LINQ query

dbContext.Customers.Where(c => c.AssetTag == assetTag).Count();

or

(from c in dbContext.Customers
 where c.AssetTag == assetTag
 select c).Count();

The generated SQL is

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Customer] AS [Extent1]
    WHERE (([Extent1].[AssetTag] = @p__linq__0) AND ( NOT ([Extent1].[AssetTag] IS NULL    OR @p__linq__0 IS NULL))) OR (([Extent1].[AssetTag] IS NULL) AND (@p__linq__0 IS NULL))
)  AS [GroupBy1]

So why does LINQ generate such complex SQL for a simple where statement?

doesn't seem that complex to me, it just creates a statement to protect your call from a null extent — Claies, Nov 20 '13 at 21:09
can you tell me how you can get this SQL Queries from Linq or Lamda .. this is toally unrelated to your question but something I am having trouble getting. — Anuj Pandey, Nov 27 '13 at 11:42
@e10 You can use the ToTraceString method (http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring(v=vs.110).aspx) or a tool like LINQPad. — Matthias Meid, Apr 09 '14 at 14:27

score 9 · Answer 1 · answered Nov 21 '13 at 21:32

9

In EF6 the database null semantics is the default comparison semantics. Note that this is a change to the default setting in EF5. In EF5 this flag was buried in ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior and by default EF would use Linq to Object comparison semantics. In EF6 it is exposed on DbContext as DbContext.Configuration.UseDatabaseNullSemantics. You can find more details here

answered Nov 21 '13 at 21:32

Pawel

31,342
4
73
104

3

Your linked msdn help page says that the default value of `DbContextConfiguration.UseDatabaseNullSemantics` is false -- so in EF6 database null semantics are NOT the default comparison semantics? – springy76 Oct 06 '15 at 17:34

score 8 · Answer 2 · answered Nov 20 '13 at 21:11

in C# string equivalency, null == null evaluates to True. null == null in the database evaluates to False. The script is verifying that either both the column value and the parameter are null, or that both are not null and they have the same string value.

WHERE 
    (
        -- neither the column nor the paramter are null and
        -- the column and the parameter have the same string value
        ([Extent1].[AssetTag] = @p__linq__0) AND 
        ( NOT ([Extent1].[AssetTag] IS NULL    OR @p__linq__0 IS NULL))
    ) 
    OR 
    (
        -- both the column value and the parameter are null
        ([Extent1].[AssetTag] IS NULL) AND 
        (@p__linq__0 IS NULL)
    )

Thanks Everybody for the explanation – Vinod Shinde Nov 20 '13 at 22:29 — Vinod Shinde, Nov 20 '13 at 22:29

score 7 · Answer 3 · answered Nov 20 '13 at 21:12

That WHERE condition is generated this way because with ANSI NULLS setting, comparing AssetTag == null will not return the corresponding rows in SQL (since in SQL world when comparing null to null the result is null). To keep the query behavior the same as a C# developer would expect, EF generates the extended WHERE clause. Note that previous versions of EF did not do so and thus did not work on databases with ANSI NULLS setting.

The GroupBy projection is there because EF supports much more complex queries before the .Count() call, such as joins, projections etc. This approach is thus more generic as it will work work all those scenarios as well.

I was going to try and explain the extent, but didn't know how to explain it despite having a superficial understanding of it. +1 for putting it so elegantly. — AaronLS, Nov 20 '13 at 21:15

score 6 · Answer 4 · answered Nov 20 '13 at 21:14

For one thing, in C# c.AssetTag == assetTag would be true if both are null. In SQL however, null compared to anything is always false. Therefore, if we want to generate a query that follows C# comparison mechanics, we must add additional conditions to ensure null compares evaluate to true if both are null:

([Extent1].[AssetTag] IS NULL) AND (@p__linq__0 IS NULL)

Why does Entity Framework 6 generate complex SQL queries for simple lookups?

4 Answers4

Linked