Closed andyfurniss4 closed 2 years ago
@andyfurniss4 could you please try flipping around the operands of the filter, and check the plan?
modelBuilder.Entity<TestEntity>()
.HasQueryFilter(t => _userTenantIds.Contains(t.TenantId) || _isUnrestricted);
EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.
BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.
I am trying to use a global query filter within a .NET API project on a per-request basis. I am injecting some user details into the DbContext and then using these for the query filter. There are two conditions that are applied in the query filter. One is a boolean which if true, allows the query to be unrestricted (i.e. no filter is applied). This is the case when the user is effectively a 'super user' and should be allowed to access everything. The second condition is applied to filter by tenant ID.
When enabling SQL logging, I can see that the query filter is applied like this in the WHERE clause:
WHERE ((@__ef_filter__p_2 = CAST(1 AS bit)) OR ([c].[TenantId] = '915a9fa9-90fb-4cfe-844b-062d0b148f69'))
When testing this in SSMS with the execution plan turned on, I can see that it's not using my index (on TenantId and a couple of other fields). When removing the first part
(@__ef_filter__p_2 = CAST(1 AS bit)
, it does use my index and is significantly faster.Is there a way I can define my query filter which will allow me to still take advantage of indexes for performance? This seems like a fairly basic requirement but I can't find anything in the documentation or any relavant issues raised by other. All the examples I've found seem to just be checking a single TenantId, without this second super user condition that I have.
EF Core version: 6 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: JetBrains Rider