EF 6 Parameter Sniffing

by

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

More Related Contents:

  1. Fastest Way of Inserting in Entity Framework
  2. Entering keys manually with Entity Framework
  3. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  4. Validation failed for one or more entities while saving changes to SQL Server Database using Entity Framework
  5. LINQ to SQL multiple tables left outer join
  6. Entity Framework error: Cannot insert explicit value for identity column in table
  7. Entity Framework. Delete all rows in table
  8. Entity Framework – stored procedure return value
  9. Retrieve an object from entityframework without ONE field
  10. Join and Include in Entity Framework
  11. Anonymous type result from sql query execution entity framework
  12. Calling a SQL User-defined function in a LINQ query
  13. Stored procedure returns int instead of result set
  14. How to use String property as primary key in Entity Framework
  15. Querying data using Entity Framework from dynamically created table
  16. There is already an open DataReader associated with this Command which must be closed first
  17. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  18. Entity Framework Stored Procedure Table Value Parameter
  19. Method cannot be translated into a store expression
  20. How can I automatically filter out soft deleted entities with Entity Framework?
  21. EF Code-First One-to-one relationship: Multiplicity is not valid in Role * in relationship
  22. LINQ to Entities only supports casting EDM primitive or enumeration types with IEntity interface
  23. Filter all queries (trying to achieve soft delete)
  24. Dynamic table name in linq [duplicate]
  25. insert datetime value in sql database with c#
  26. EF5 Getting this error message: Model compatibility cannot be checked because the database does not contain model metadata
  27. How to pass string array in SQL parameter to IN clause in SQL
  28. Async PartialView causes “HttpServerUtility.Execute blocked…” exception
  29. Cannot find command ‘dotnet ef’
  30. The ObjectContext instance has been disposed and can no longer be used for operations that require a connection [duplicate]

Leave a Comment