Scott Wegner Scott Wegner - 4 months ago 23
SQL Question

Entity Framework queries miss filtered index WHERE BIT field = 0

I've noticed that Entity Framework translates LINQ queries with negative boolean filters such that the generated query plan won't use a filtered index. For example, the query:

context.Foo.Count(f => !f.IsActive)

generates the SQL statement:

[GroupBy1].[A1] AS [C1]
COUNT(1) AS [A1]
FROM [dbo].[Foos] AS [Extent1]
WHERE [Extent1].[IsActive] <> cast(1 as bit)
) AS [GroupBy1]

Notice the
clause uses
[IsActive] <> cast(1 as bit)
, rather than the more intuitive
[IsActive] = 0
. This becomes an issue when using filtered indexes. The plan for the above query will not use the following index:

CREATE INDEX IX_Foo_IsActive ON Foos (IsActive) WHERE (IsActive = 0)

I suspect the reason that EF generates queries this way has something to do with DB null semantics, but this happens even with non-nullable bit fields. I've verified that writing the filtered index with EF's syntax (
IsActive <> 1
) fixes the issue, but that would break any non-EF queries using the more common syntax.

Is there a better work around?

Full example program here: The entity type used above is:

public class Foo
public int Id { get; set; }
public bool IsActive { get; set; }


It's not unusual that for some strange reason, sometimes we don't see something which is really obvious: do a direct translation of your DB predicate to a C# predicate, i.e.

WHERE IsActive = 0

is translated to

f => f.IsActive = false

You have to stop thinking in C# and start thinking in SQL ;)