entity-framework

How to add OPTION RECOMPILE into Entity Framework


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?


Solution

  • 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);
      }
    }