How to add OPTION RECOMPILE into Entity Framework

Question

I am experiencing parameter sniffing problem sometimes. So I would like to add OPTION (RECOMPILE) to the end of query to fix it. How can I do it in EF 6?

Answer 1

I implemented IDbCommandInterceptor interface to fix the parameter sniffing error.

The usage of the new interceptor:

  using (var dataContext = new AdventureWorks2012Entities())
  {
    var optionRecompileInterceptor = new OptionRecompileInterceptor();
    DbInterception.Add(optionRecompileInterceptor);

    string city = "Seattle";
    var seattle = (
      from a in dataContext.Addresses
      where a.City == city
      select a).ToList();

    DbInterception.Remove(optionRecompileInterceptor); //Remove interceptor to not affect other queries
  }

Implementation of the interceptor:

public class OptionRecompileInterceptor : DbCommandInterceptor
{
  static void AddOptionToCommand(DbCommand command)
  {
    string optionRecompileString = "\r\nOPTION (RECOMPILE)";
    if (!command.CommandText.Contains(optionRecompileString)) //Check the option is not added already
    {
      command.CommandText += optionRecompileString;
    }
  }


  public override void NonQueryExecuting(
    DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
  {
    AddOptionToCommand(command);
  }

  public override void ReaderExecuting(
    DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
  {
    AddOptionToCommand(command);
  }

  public override void ScalarExecuting(
    DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
  {
    AddOptionToCommand(command);
  }
}