1

Closed

EF6 generates sql that does not utilize index

description

EF6 sql generation adds special checks for null that sometimes perplex MS SQL optmizator and it stops using index on query field.
for instance, query
  from b in db.Customers where b.ManagerId == managerId
gives SQL
WHERE (([Extent1].[ManagerId] = @p__linq__0) 
AND ( NOT ([Extent1].[ManagerId] IS NULL OR @p__linq__0 IS NULL))) 
OR (([Extent1].[ManagerId] IS NULL) AND (@p__linq__0 IS NULL))
instead of EF5's
WHERE [Extent1].[ManagerId] = @p__linq__0
In half of cases MS SQL 2012 does not honor the index on the fields like ManagerId and makes full table scan.

Some details are here:

http://stackoverflow.com/questions/19672799/ef6-sql-generation-for-where-nullable-columns-equals
Closed Nov 5, 2013 at 6:55 PM by divega
We will use https://entityframework.codeplex.com/workitem/1598 to track all query issues related to UseDatabaseNullSemantics.

comments

moozzyk wrote Oct 30, 2013 at 5:18 PM

This seems to be a result of UseDatabaseNullSemantics being set to false by default. See this for more details: https://entityframework.codeplex.com/workitem/1598

BenKalegin wrote Oct 30, 2013 at 6:10 PM

Thanks,
setting db.Configuration.UseDatabaseNullSemantics = true does the trick and performance is back.
Gone to fix every project to add magic phrase in context constructor -)