Implementing Data Isolation with EF Core Global Query Filters

The Problem: Leaking Deleted or Multi-Tenant Data

In large-scale applications, forgetting a where isDeleted == false or where TenantId == currentTenantId clause in a single repository method can lead to critical data leaks or the accidental resurrection of deleted records. Relying on developers to manually apply these filters across every query is a high-risk strategy.

The solution is the Global Query Filter. This EF Core feature allows you to define a predicate at the model level that the query provider automatically injects into the WHERE clause of every generated SQL statement for a specific entity type.

The Smallest Suitable Design

To avoid repeating configuration for every entity, use a marker interface. This ensures a consistent contract across your domain models.

1. Define the Contract

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
}

2. Apply the Filter in OnModelCreating

Configure the filter within your DbContext. For multi-tenancy, the TenantId must be provided to the context via dependency injection so the filter can reference a dynamic value.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Soft Delete Filter
    modelBuilder.Entity().HasQueryFilter(p => !p.IsDeleted);

    // Multi-tenant Filter
    // _tenantService provides the ID of the currently authenticated user's organization
    modelBuilder.Entity().HasQueryFilter(o => o.TenantId == _tenantService.GetCurrentTenantId());
}

Trust and Data Boundaries

The trust boundary is established at the DbContext level. By moving the filtering logic from the Service Layer to the Data Access Layer, you create a secure by default environment. Developers interacting with DbSet do not need to know the isolation logic exists; the framework enforces it before the query ever hits the database.

Critical Boundary Exception: Global filters are ignored when using FromSqlRaw or FromSqlInterpolated. If your application uses raw SQL for performance optimization, the trust boundary is broken, and you must manually append the filtering criteria to the SQL string.

Operational Checks and Performance

Because EF Core injects these filters into every query, they can inadvertently cause full table scans if the filtered columns are not indexed.

Diagnostic Decision Matrix

Scenario	Risk	Required Action
High volume of soft-deleted rows	Index fragmentation / Scan overhead	Create a filtered index: CREATE INDEX IX_Active ON Table(Id) WHERE IsDeleted = 0
Multi-tenant partitioning	Slow lookups across tenants	Include TenantId as the leading column in composite indexes
Complex navigation filters	Inefficient JOINs	Analyze execution plan for nested loops

Failure Modes

• The Invisible Record Bug: A developer attempts to find a record by ID, but it returns null because it is soft-deleted. This is intended behavior but often confuses those unfamiliar with the filter.
• Performance Degradation: Adding a filter to a table with millions of rows without a corresponding index will spike CPU and I/O on the database server.
• Static Context Leak: If the TenantId is cached statically rather than resolved per-request, one user may see another user's data.

Bypassing Filters and Design Changes

There are cases where you must ignore the filter, such as an administrative Recycle Bin view or a data migration script. Use the .IgnoreQueryFilters() method on the IQueryable chain.

// Run this in an admin-level service with appropriate permissions
var allProducts = await _context.Products
    .IgnoreQueryFilters()
    .Where(p => p.IsDeleted)
    .ToListAsync();

When to change this design: If your business requirements evolve so that 80% of your queries need to bypass the filter, the design is no longer smallest suitable. At that point, move the logic back to a Specification pattern or a dedicated Repository method to avoid the overhead of applying and then ignoring the filter.

Verification Steps

To verify the implementation is working as expected, perform these three checks:

1. SQL Inspection: Enable sensitive data logging in your DbContext options. Run a simple _context.Products.ToList() and verify the generated SQL contains WHERE [p].[IsDeleted] = 0.
2. Negative Test: Insert a record with IsDeleted = true. Attempt to retrieve it via a standard query; it should return null.
3. Bypass Test: Retrieve the same record using .IgnoreQueryFilters(); it should be returned successfully.