EF 6 Parameter Sniffing

It's possible to use the interception feature of EF6 to manipulate its internal SQL commands before executing them on DB, for instance adding option(recompile) at the end of the command:

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

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

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

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

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

To use it, add the following line at the beginning of the application:

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

I like VahidN's solution, do up vote him, but I want more control of when it happens. It turns out that DB Interceptors are very global, and I only wanted this to happen on specific contexts in specific scenarios.

Here we are setting the ground work to also support adding other query hints, that could be turned on and off as desired.

Since I often expose the method for passing a connection string, I also included support for that.

Below would give your context a flag to enable/disable the hint programatically, by extending the partial class EF generates. We also threw the small piece of reused code in the Interceptor into its own method.

Small Interface

public interface IQueryHintable
{
    bool HintWithRecompile { get; set; }
}

DB Command Interceptor

public class OptionHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

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

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

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

    private static void AddHints<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        var context = interceptionContext.DbContexts.FirstOrDefault();
        if (context is IQueryHintable)
        {
            var hints = (IQueryHintable)context;

            if (hints.HintWithRecompile)
            {
                addRecompileQueryHint(command);
            }
        }
    }

    private static void addRecompileQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

Extending Entity Context to Add IQueryHintable

public partial class SomeEntities : DbContext, IQueryHintable
{
    public bool HintWithRecompile { get; set; }

    public SomeEntities (string connectionString, bool hintWithRecompile) : base(connectionString)
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (bool hintWithRecompile) : base()
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (string connectionString) : base(connectionString)
    {
    }

}

Register DB Command Interceptor (global.asax)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

Enable context wide

    using(var db = new SomeEntities(hintWithRecompile: true) )
    {
    }

Turn On or Off

    db.HintWithRecompile = true;
    // Do Something
    db.HintWithRecompile = false;

I called this HintWithRecompile, because you might also want to implement a HintOptimizeForUnknown , or other query hints.

Same for me as for @Greg, enabling this system wide was not an option, so I wrote this small utility class that can temporary add option(recompile) to queries executed within an OptionRecompileScope.

Example usage

using (new OptionRecompileScope(dbContext))
{
    return dbContext.YourEntities.Where(<YourExpression>).ToList();
}

Implementation

public class OptionRecompileScope : IDisposable
{
    private readonly OptionRecompileDbCommandInterceptor interceptor;

    public OptionRecompileScope(DbContext context)
    {
        interceptor = new OptionRecompileDbCommandInterceptor(context);
        DbInterception.Add(interceptor);
    }

    public void Dispose()
    {
        DbInterception.Remove(interceptor);
    }

    private class OptionRecompileDbCommandInterceptor : IDbCommandInterceptor
    {
        private readonly DbContext dbContext;

        internal OptionRecompileDbCommandInterceptor(DbContext dbContext)
        {
            this.dbContext = dbContext;
        }

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

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private static void AddOptionRecompile(IDbCommand command)
        {
            command.CommandText += " option(recompile)";
        }

        private bool ShouldIntercept(IDbCommand command, DbCommandInterceptionContext interceptionContext)
        {
            return 
                command.CommandType == CommandType.Text &&
                command is SqlCommand &&
                interceptionContext.DbContexts.Any(interceptionDbContext => ReferenceEquals(interceptionDbContext, dbContext));
        }
    }
}