C# Entity Framework use SQL options/hints

Question

I am working on some search optimizations in a WPF application.

There is a search XAML view, that uses a SQL Server view with IQueryable.

In SQL Server, I can use OPTION(QUERYTRACEON 8649) when I get the SELECT part from the view, but in the view itself, I can't use it. This OPTION speeds up my query more than 3 times. I clear cache each time I execute it.

Because of this SQL Server view's limitation, I need to somehow add this to C# code.

So, how can I add this OPTION to be used in IQueryable?

Answer 1

You can add query hints by using interceptor.

Read details from this sample by microsoft.

Also, you have to register interceptor class like this.

private void DependencyInjection(IServiceCollection services)
{
 services.AddDbContext<SampleDbContext>(options =>
                options.UseSqlServer("some connection string") 
                       .AddInterceptors(new TaggedQueryCommandInterceptor())
 );
}