c# - EF 6 Parameter Sniffing

Question

I have a dynamic query that is just too large to put here. Safe to say that in it's current form it utilizes a CLR procedure to dynamically build joins based upon the number of search parameters passed then takes that result and joins it to more detailed tables to bring back attributes important to the end-user. I have converted the entire query into LINQ to Entities and what I have found is that the SQL that it produces is efficient enough to do the job, however running via EF 6, the query timesout. Taking the resulting SQL and running it in SSMS runs in 3 or less seconds. I can only imagine that my problem is parameter sniffing. I have tried updating statistics on every table in the database and this has not solved the problem.

My Question is:

Can I somehow embed options like an "OPTION RECOMPILE" via EF?

Answer 1

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());