Query Hint with Interceptor #10169

AspNetZero v8.4, Abp 5.5, Mvc/jQuery.

ORIGINAL MESSAGE SENT BY EMAIL

We have a query that suffers from parameter sniffing, where execution of the EF-generated query takes 10 seconds to return 1 row, but if you can convert it to T-SQL, it executes immediately. If I were to solve this problem using a Microsoft-centric mindset, I would add code similar to this link: Interceptors - EF Core | Microsoft Docs

var blogs1 = context.Blogs.TagWith("Use hint: recompile plan").ToList();

That EF statement would then generate SQL that looked like this: -- Use hint: robust plan

SELECT [b].[Id], [b].[Name] FROM [Blogs] AS [b] OPTION (RECOMPILE)

In the EF6 days, I used this same technique with success, and it looked like this: How to add OPTION RECOMPILE into Entity Framework - Stack Overflow

Is there a sample that you can point to where the ASP.NET Zero classes can enable per-context/per-statement query hint generation?

Thanks for any help.

<br>

ORIGINAL RESPONSE RECEIVED BY EMAIL

I think you are trying to detect the cause of the problem. If so, you can inject IDbContextProvider and get the DbContext using this class and execute a method like you have shared in your email for including recompile plan.

If this is not for detecting the problem, then you can create a custom repository (https://aspnetboilerplate.com/Pages/Documents/Repositories#custom-repositories)  and place the same query in it.

<br> <br>

UPDATED QUESTION

Is there a ready-made sample available somewhere that would show how to implement the EF Core Interceptor (TaggedQueryCommandInterceptor [ref: https://docs.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors]) in an ABP repository? We have code that looks like this in an AppService:

private readonly IRepository&lt;OrganizationUnit, long> _organizationUnitRepository;

public async Task&lt;GetUserForEditOutput> GetUserForEdit(NullableIdDto&lt;long> input) { var allOrganizationUnits1 = await _organizationUnitRepository.GetAllListAsync(); … }

I would love to be able to specify the query hint on an individual query, based on our investigation into whether a specific query will need it or not. So the last statement would maybe look something like: var allOrganizationUnits1 = await _organizationUnitRepository.OptionRecompile().GetAllListAsync();

and the resulting query would use the EF interceptor to add the OPTION RECOMPILE SQL query hint.

Thanks again.