We can use Global Query Filters at an entity level to attach an additional LINQ where operator whenever the entity type is queried.
Consider the following DbContext.
public class Customer
{
public int Id { get; set; }
public string TenantId { get; init; }
public string Name { get; init; }
public bool IsDeleted { get; set; }
} public class MyDbContext(string tenantId) : DbContext { public DbSet<Customer> Customers { get; set; } override protected void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Customer>() .HasQueryFilter(x => x.TenantId == tenantId); } }
And we can do something like this.
string tenantId = "Tenant1";
using var context = new MyDbContext(tenantId);
await context.Customers.AddRangeAsync(
[
new Customer
{
TenantId = tenantId,
Name = "John Doe"
},
new Customer
{
TenantId = tenantId,
Name = "Jane Doe",
IsDeleted = true
},
new Customer
{
TenantId = "Tenant2",
Name = "Jim Doe"
}
]);
await context.SaveChangesAsync();
foreach (Customer? customer in await context.Customers.ToListAsync()) { Console.WriteLine($"Customer: {customer.Name}, Tenant: {customer.TenantId}"); }
When we run above code, the executed query is something below.
SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[TenantId] = @__P_0
As you can see, Query Filter was attached and we are getting the expected
result.
Now prior to EF Core 10.0, if for some reason, we add another Query
Filter by doing something like below;
modelBuilder.Entity<Customer>()
.HasQueryFilter(x => x.TenantId == tenantId);
modelBuilder.Entity<Customer>()
.HasQueryFilter(x => !x.IsDeleted);
SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[IsDeleted] = CAST(0 AS bit)
Only the last Query Filter was used.
This would not be the desired output. Prior to EF Core 10, when multiple
filters are configured, prior filters are overridden.
The workaround is,
modelBuilder.Entity<Customer>()
.HasQueryFilter(x => x.TenantId == tenantId && !x.IsDeleted);
With EF Core 10.0, we can now define multiple Query Filters, but each filter
has to be given a name.
modelBuilder.Entity<Customer>()
.HasQueryFilter("TenantFilter", x => x.TenantId == tenantId)
.HasQueryFilter("SoftDeletionFilter", x => !x.IsDeleted);
And this would generate the following query for the above code.
SELECT [c].[Id], [c].[IsDeleted], [c].[Name], [c].[TenantId]
FROM [Customers] AS [c]
WHERE [c].[TenantId] = @P AND [c].[IsDeleted] = CAST(0 AS bit)
And also we can ignore Query Filters by doing something like below.
// Query counts with a specific Query Filter ignored
int tenantCustomersCountIncludingDeleted = await context.Customers
.IgnoreQueryFilters(["SoftDeletionFilter"])
.CountAsync(); // 2
// Query counts with all Query Filters ignored
int allCustomersCount = await context.Customers
.IgnoreQueryFilters()
.CountAsync(); // 3
More read:
What's New in EF Core 10
