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<OrganizationUnit, long> _organizationUnitRepository;
public async Task<GetUserForEditOutput> GetUserForEdit(NullableIdDto<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.
