1

I have a query that I suspect is guilty of parameter-sniffing. I'd like to apply an interceptor like the one found here, but I don't want any other queries affected.

The code used there is: DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

What is the scope of this command and how do you limit the scope of an interceptor to just a single query?

My first thought was to do something like this:

var interceptor = new OptionRecompileHintDbCommandInterceptor();
try
{
    DbInterception.Add(interceptor);

    // Do query
}
finally
{
    DbInterception.Remove(interceptor);
}

But is this same to do or could it affect queries on other threads?

Update Another thought occurred to me. If I can't control when an interceptor is applied, is there a way to put the logic in a conditional if statement in the interceptor and then supply some kind of context to the interceptor so it knows when to apply the logic?

Community
  • 1
  • 1
adam0101
  • 29,096
  • 21
  • 96
  • 174

1 Answers1

3

Two options I can think of for this, since it is at the global level, is in the query you want to single out, you can do something where you say 9 = 9 or something that is always true so you know it is the query you want. Then you could find and replace that where clause part with an empty string and then continue on with whatever you were going to do.

The other is quite a bit sneakier, but should work. In your interceptor code, you can do this:

var ctx = interceptionContext.DbContexts.FirstOrDefault() As MyDbContext;

But then you should be able to cast it to your specific DbContext type. On that DbContext type, you could just add some boolean property that would indicate this is the query you are running. Then, right before running it, you can set that to true wherever else and you'd be able to intercept it.

Daniel Lorenz
  • 4,178
  • 1
  • 32
  • 39