Implementing Soft Deletes with EF Core Global Query Filters

The Problem: The 'Forgotten Where' Clause

In many production systems, permanently deleting data is a risk. Whether for audit trails or accidental deletion recovery, developers often implement a "soft delete"—marking a record as deleted via a boolean flag (e.g., IsDeleted) rather than removing the row from the database.

The technical challenge is consistency. Every single LINQ query across your entire application must include .Where(x => !x.IsDeleted). If a developer forgets this clause in one obscure service or a new API endpoint, the application may leak "deleted" data to the end user, leading to data integrity bugs that are difficult to trace.

The Solution: Global Query Filters

Entity Framework Core provides Global Query Filters, which allow you to define a predicate at the model level. Once configured, EF Core automatically appends this logic to the WHERE clause of every query generated for that entity. This transforms soft-delete logic from a manual requirement into a systemic guarantee.

Configuring the Filter

Filters are defined within the OnModelCreating method of your DbContext. By using the HasQueryFilter method, you tell the model builder exactly which records should be visible by default.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Apply a global filter to the Product entity
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);
}

Practical Implementation Example

Consider a scenario where you have a Product entity and a Category entity. If a product is soft-deleted, it should disappear from the storefront, but an administrator should still be able to see it in a recovery dashboard.

The Entity Model

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public bool IsDeleted { get; set; } // The soft-delete flag
}

Querying Data

When you execute a standard query, EF Core handles the filter automatically. You do not need to specify the IsDeleted state.

// Run this in your Service layer
// SQL generated: SELECT * FROM Products WHERE IsDeleted = 0
var activeProducts = await _context.Products.ToListAsync();

Bypassing the Filter for Admin Tasks

There are cases where you explicitly need the deleted data (e.g., an "Undo Delete" feature). Use the IgnoreQueryFilters() method to tell EF Core to omit the predicate for a specific request.

// Run this in your Admin/Recovery layer
// SQL generated: SELECT * FROM Products
var allProductsIncludingDeleted = await _context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Critical Trade-offs and Limitations

While global filters simplify development, they introduce specific behaviors that can cause confusion if not managed:

• Navigation Property Surprises: If you load an entity that has a relationship to a soft-deleted entity, the navigation property will be null (or the collection will be empty), even if the record exists in the database. This can lead to unexpected NullReferenceException errors if your code assumes the relationship always exists.
• Raw SQL Bypass: Global filters only apply to LINQ queries. If you use FromSqlInterpolated or FromSqlRaw to execute manual SQL, EF Core does not inject the filter. You must manually add WHERE IsDeleted = 0 to your raw SQL strings.
• Performance Impact: Because the filter is applied to every query, the filtered column (IsDeleted) should be indexed. Without a proper index, the database may perform full table scans, degrading performance as the dataset grows.

Verification Checklist

To ensure your soft-delete implementation is working correctly, perform these three checks:

1. Standard Query: Create a record with IsDeleted = true. Query the entity via LINQ and verify the record is not returned.
2. Admin Query: Use .IgnoreQueryFilters() and verify the record is returned.
3. SQL Log: Enable sensitive data logging or check the EF Core console output to verify that WHERE [p].[IsDeleted] = 0 is actually present in the generated T-SQL.