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