Entity Framework query missing a filtered index on SQL Server 2012

Question

I have this EF query: (only kept the essential part)

int maxRetryCount = 5;
var erroredArchiveFilesQuery =
  transitionLogSessionContext.Set<ArchivedFile>().Where(f => 
  f.RetryCount < maxRetryCount
).Take(maxBatchSize);

It misses the filtered index available.

Whereas when removing the variable maxRetryCount as such

var erroredArchiveFilesQuery =
transitionLogSessionContext.Set<ArchivedFile>().Where(f => 
f.RetryCount < 5 && 
).Take(maxBatchSize);

will use the filtered index.

The actual SQL from the first EF query...

SELECT TOP (500) 
    [Extent1].[Id] AS [Id], 
     ..
FROM  
    [ArchivedFile] AS [Extent1]
WHERE 
    ([Extent1].[RetryCount] < @p__linq__0 ) 

The filtered index contains the column RetryCount and a filter 'RetryCount < 5'

How can I make an ef query with a variable that will hit filtered index?

I assume the problem is in the EF statement being prepared so it can be reused, and this confuses the SQL Server.

Answer 1

You need to ensure that SQL Server recompiles the plan each time based upon the actual value of the parameter maxRetryCount. This is not easy in EF but can be done using a custom database interceptor to add option (recompile) hint to your query.

See details here SimpleTalk Article

public class RecompileDbCommandInterceptor : IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command,  DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if(!command.CommandText.EndsWith(" option(recompile)"))
        {
            command.CommandText +=  " option(recompile)";
        }
    }
}

You can use it like this:

var interceptor = new RecompileDbCommandInterceptor();
DbInterception.Add(interceptor);

int maxRetryCount = 5;
var erroredArchiveFilesQuery =
  transitionLogSessionContext.Set<ArchivedFile>().Where(f => 
  f.RetryCount < maxRetryCount
).Take(maxBatchSize);

DbInterception.Remove(interceptor);

Note that this interception is enabled globally, not for the specific instance of the context, so you probably want to disable it again so that other queries aren’t affected.