Adding a query hint when calling Table-Valued Function

Question

I'm calling a Table-Valued Function from entity framework and need to be able to add the option (recompile) to it because the execution plan it picks up is not optimal. Running the query in SQL Server Management Studio, it would look something like this:

select 
       * 
from dbo.fDE_myquery(0, 0, 3309, '7/1/2013', '7/1/2014', 0, 0)
option (recompile)

By from EF, there's no way to add that hint, AFAIK. The EF part looks something like:

var query = from f in ctx.fDE_myQuery(aBool, anotherBool, StartDate, 
            EndDate, someInt, moreBool)
            select f;

I saw this question:

How do I control parameter sniffing and/or query hints in entity framework?

But it's old, and the accepted solution doesn't really give enough information on how to actually implement the suggested solution (use plan guides) with entity framework. If that is the only solution, how do you get entity framework to use a plan guide anyway?

Answer 1

I came across this:

https://entityframework.codeplex.com/wikipage?title=Interception

And it appears that you can do something like this:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        command.CommandText += " option (recompile)";
        base.ReaderExecuting(command, interceptionContext);
    }
}

And register it like this (I did it in Application_Start of global.asax.cs):

DbInterception.Add(new HintInterceptor());

And it will let you alter the CommandText. The only problem is that it's now attached for every reader query which might be a problem as some of them might be negatively impacted by that hint. I'm guessing I can do something with the context to figure out if the hint is appropriate or not, or worse case I could examine the CommandText itself.

Doesn't quite seem the most elegant or fine-grained solution.

Edit: From the interceptorContext, you can get the DbContexts, so I defined an interface that looks like this:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

And then created a class that derives from my original DbContext (generated by EF) and implements the above interface. Then I changed my interceptor to look like this:

public class HintInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        if (interceptionContext.DbContexts.Any(db => db is Dal.IQueryHintContext))
        {
            var ctx = interceptionContext.DbContexts.First(db => db is Dal.IQueryHintContext) as Dal.IQueryHintContext;
            if (ctx.ApplyHint)
            {
                command.CommandText += string.Format(" option ({0})", ctx.QueryHint);
            }
        }
        base.ReaderExecuting(command, interceptionContext);
    }
}

Now to use it, I create a context using my derived class instead of the original, set QueryHint to whatever I want it to be (recompile in this case) and set ApplyHint right before I execute the command and set it back to false afterwards.

To make this all a little more self-contained, I ended up defining an interface like this:

public interface IQueryHintContext
{
    string QueryHint { get; set; }
    bool ApplyHint { get; set; }
}

And extended my db context like this (you could, of course, just use a partial class to extend the EF generated class as well):

public class MyEntities_Ext : MyEntities, IQueryHintContext
{
    public string QueryHint { get; set; }
    public bool ApplyHint { get; set; }
}

And then, to make the turn-on, turn-off part a little easier to handle, I defined this:

public class HintScope : IDisposable
{
    public IQueryHintContext Context { get; private set; }
    public void Dispose()
    {
        Context.ApplyHint = false;
    }

    public HintScope(IQueryHintContext context, string hint)
    {
        Context = context;
        Context.ApplyHint = true;
        Context.QueryHint = hint;
    }
}

Now to use it, I can do just this:

using (var ctx = new MyEntities_Ext()) 
{
    // any code that didn't need the query hint
    // ....
    // Now we want the query hint
    using (var qh = new HintScope(ctx, "recompile"))
    {
        // query that needs the recompile hint
    }
    // back to non-hint code
}

This maybe slightly overkill and could be developed further (for example, using an enum for available hints instead of a string - or subclassing a recompile query hint so you don't need to specify the string recompile every time and risk a typo), but it solved my immediate problem.