Base solution for your next web application
Open Closed

IRepository generated SQL index scan #10320


User avatar
0
conorcorr created
  • What is your product version? 3.4
  • What is your product type (Angular or MVC)? AngularJS
  • What is product framework type (.net framework or .net core)? .Net Framework

If issue related with ABP Framework

  • What is ABP Framework version? 1.5.1

We have an entity, TagEntity, which inherits from FullAuditedEntity and implements IMustHaveTenant.

To get all tags belonging to the current tenant we perform the following query (where _tagRepository is an instance of IRepository):

var tags = _tagRepository.GetAll().ToList();

This results in the database performing a clustered index scan on the Tags table, despite there being an index on TenantId and IsDeleted.

Whereas, the following SQL query gets a non clustered index seek as expected:

SELECT * FROM Tags WHERE TenantId = 3 AND IsDeleted = 0

It appears that this issue is related to the use of dynamic data filters and IMustHaveTenant generating clauses such as:

(([Var_8].[TenantId] = @DynamicFilterParam_000005) OR ( CAST( [Var_8].[TenantId] AS int) IS NULL) )

Is there any way that we can write queries using IRepository repositories so that the generated SQL doesn’t result in an index scan?


1 Answer(s)
  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi @conorcorr

    Is it possible for you to create an issue on GitHub ? We can work on this and try to find the problem.

    Thanks,