Global Query Filters in EF Core: Building Reliable Multi‑Tenant and Soft‑Delete Boundaries

Problem Statement

In many SaaS applications, a single database hosts data for dozens or hundreds of tenants. A common requirement is to prevent a tenant from seeing another tenant’s data and to hide records that have been logically deleted (soft‑deleted). Manual filtering in every query is error‑prone and hard to maintain. Entity Framework Core provides Global Query Filters – predicates automatically appended to every query for a given entity type. When applied correctly, they enforce data‑level boundaries at the ORM level, reducing the risk of accidental data leakage.

Requirements

• All tenant‑specific entities must expose a TenantId property of a comparable type (e.g., Guid or int).
• Soft‑deletable entities must expose an IsDeleted boolean flag.
• The database schema must have indexes on TenantId and IsDeleted to keep query performance acceptable.
• Application code must be able to supply the current tenant context at query time.
• Administrative operations sometimes need to bypass tenant or soft‑delete filters.

Minimal Suitable Design

The core of the design is a TenantAwareDbContext that registers global filters in OnModelCreating. The filters reference a service that holds the current tenant id, allowing the filter to be dynamic per request.

public interface ITenantProvider
{
    Guid CurrentTenantId { get; }
}

public class TenantAwareDbContext : DbContext
{
    private readonly ITenantProvider _tenantProvider;

    public TenantAwareDbContext(DbContextOptions options, ITenantProvider tenantProvider)
        : base(options) => _tenantProvider = tenantProvider;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply Tenant filter to all entities that have a TenantId property
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (entityType.ClrType.GetProperty("TenantId") != null)
            {
                var parameter = Expression.Parameter(entityType.ClrType, "e");
                var tenantProp = Expression.Property(parameter, "TenantId");
                var tenantValue = Expression.Constant(_tenantProvider.CurrentTenantId);
                var equalExpr = Expression.Equal(tenantProp, tenantValue);
                var lambda = Expression.Lambda(equalExpr, parameter);
                modelBuilder.Entity(entityType.ClrType).HasQueryFilter((dynamic)lambda);
            }
        }

        // Apply soft‑delete filter to all entities that have an IsDeleted property
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (entityType.ClrType.GetProperty("IsDeleted") != null)
            {
                var parameter = Expression.Parameter(entityType.ClrType, "e");
                var isDeletedProp = Expression.Property(parameter, "IsDeleted");
                var notDeleted = Expression.Equal(isDeletedProp, Expression.Constant(false));
                var lambda = Expression.Lambda(notDeleted, parameter);
                modelBuilder.Entity(entityType.ClrType).HasQueryFilter((dynamic)lambda);
            }
        }
    }
}

Key points:

• The filter is defined once per entity type, not per query.
• Using ITenantProvider allows the filter to change per HTTP request without re‑creating the context.
• The code uses reflection to apply the filter to any entity that follows the naming convention, reducing boilerplate.

Trust/Data Boundaries

Global filters guarantee that any LINQ query executed against the DbContext respects the tenant and soft‑delete boundaries. The boundaries are enforced at the database level because the filter is translated into a WHERE clause in the generated SQL. This means:

• Even when navigation properties are loaded via Include, the filter is applied to the related entities.
• Raw SQL queries executed with FromSqlRaw bypass the filter, so developers must be vigilant. Explicit documentation and code reviews should enforce that raw SQL is only used for administrative tasks where filters are intentionally ignored.
• When using IgnoreQueryFilters(), the application must ensure it is not inadvertently exposing data from other tenants or soft‑deleted records to end users.

Operational Checks

To verify that the filters are working as expected, perform the following checks in a test or staging environment.

1. SQL Inspection

   Execute a simple query and call ToQueryString() to capture the generated SQL. Verify that the WHERE clause contains both TenantId = @p0 and IsDeleted = 0 predicates.

   var sql = context.Customers
       .Where(c => c.Name.Contains("Acme"))
       .ToQueryString();
   Console.WriteLine(sql); // Should include TenantId and IsDeleted filters
   

2. Integration Test for Tenant Isolation

   Seed the database with two tenants (A and B). Query for tenants A and assert that no records from tenant B appear.

   // Seed
   await SeedTenantsAsync();
   
   // Act
   var tenantARecords = await context.Customers
       .Where(c => c.TenantId == tenantAId)
       .ToListAsync();
   
   // Assert
   Assert.All(tenantARecords, c => Assert.Equal(tenantAId, c.TenantId));
   

3. Soft‑Delete Bypass Test

   Mark a record as IsDeleted = true. Query normally and confirm it is excluded. Then query with IgnoreQueryFilters() and confirm the record is returned.

   var deleted = await context.Customers
       .IgnoreQueryFilters()
       .FirstAsync(c => c.Id == deletedId);
   Assert.True(deleted.IsDeleted);
   

4. Performance Check

   Run a query that returns a large result set and capture the execution plan. Ensure that the TenantId and IsDeleted columns are used in the index seek.

Failure Modes

• Missing Indexes

  Without indexes on TenantId or IsDeleted, the filter forces a full table scan. This can lead to performance regressions as tenant data grows.

• Dynamic Filter Changes

  If ITenantProvider.CurrentTenantId changes mid‑request (e.g., due to a tenant switch), the filter will apply the new value to all subsequent queries, potentially leaking data. The provider should be immutable per request.

• Navigation Property Quirks

  In EF Core 3.x, Include queries sometimes did not honor global filters on the included entities. This behavior was fixed in 5.x, but older projects may experience unexpected data leakage.

• Raw SQL Bypass

  Any developer using FromSqlRaw without awareness of the filter will inadvertently expose data. Code reviews and static analysis can mitigate this risk.

• Administrative Overrides

  When IgnoreQueryFilters() is used, accidental leaks can occur if the developer forgets to re‑apply tenant constraints in the same query.

When the Design Should Change

• High‑Throughput Reporting

  For cross‑tenant reporting, it may be more efficient to materialize a separate read‑only view per tenant or use a dedicated reporting database. Global filters then become a performance bottleneck.

• Complex Multi‑Tenant Rules

  If tenant visibility depends on more than a single TenantId (e.g., role‑based access, hierarchical tenants), a simple global filter may not suffice. In that case, consider a policy‑based approach or a custom query interceptor.

• Soft‑Delete Alternatives

  Some scenarios benefit from a DeletedAt timestamp instead of a boolean flag. The filter logic can be adapted, but the index strategy and query patterns need to change accordingly.

• Legacy Database Support

  If the database schema cannot be altered to add TenantId columns, you may need to implement tenant filtering at the application layer or use database views.

Practical Checklist

Checklist Item	Action	Verification
TenantId column present	Add to all tenant‑specific entities	Run migration script; inspect schema
IsDeleted column present	Add to soft‑deletable entities	Run migration script; inspect schema
Indexes on TenantId & IsDeleted	Create non‑clustered indexes	Query plan shows index seek
Global filters configured	Implement OnModelCreating as shown	SQL contains WHERE clauses
TenantProvider immutable per request	Use scoped service	Unit test with multiple tenants
Raw SQL usage documented	Code review policy	Static analysis finds FromSqlRaw calls

Conclusion

EF Core Global Query Filters provide a clean, declarative way to enforce multi‑tenant isolation and soft‑delete semantics. By carefully designing the filter logic, ensuring proper indexing, and validating through SQL inspection and integration tests, you can reduce the risk of accidental data leakage while keeping the codebase maintainable. Keep an eye on performance and be ready to adjust the pattern when reporting or complex tenancy rules become a bottleneck.