Choosing a Soft‑Delete Strategy in Entity Framework Core: Global Query Filters vs Manual Where vs Interceptor

Decision: Use EF Core Global Query Filters for Soft Delete

When you need to hide logically deleted rows (e.g., IsDeleted = true) from all queries without repeating Where(e => !e.IsDeleted) everywhere, the built‑in Global Query Filter feature in EF Core 5.0+ (or 2.0+ for basic filters) centralizes the logic in the model. This guide outlines the decision constraints, compares alternatives, explains trade‑offs, and shows a concrete implementation and validation.

Constraints

• EF Core version 5.0 or later (basic filters work from 2.0).
• The entity must expose a boolean IsDeleted property (nullable or non‑nullable).
• The filter is configured in OnModelCreating using HasQueryFilter.

Options Comparison

Option	Implementation effort	Runtime overhead	Maintainability	Provider compatibility	Ability to bypass
Global Query Filter (built‑in)	Low – add property and one HasQueryFilter call	None after model compilation; filter is baked into query generation	High – single place to change logic	All EF Core providers (SQL Server, SQLite, PostgreSQL, etc.)	Requires IgnoreQueryFilters on the query
Manual Where in each repository/query	Medium – must remember to add clause everywhere	Negligible – extra predicate per query	Low – easy to miss a query, leading to inconsistent results	All providers	Trivial – just omit the clause
SQL Interceptor that rewrites SELECT	High – write and register an interceptor, handle expression rewriting	Low to moderate – interceptor runs for every query, may affect caching	Medium – interceptor code must be maintained	Provider‑specific SQL generation may break with provider changes	Can bypass by disabling the interceptor or using raw SQL

Trade‑offs

Global Query Filters add no per‑query overhead after the model is compiled and work uniformly across providers. The main caveat is that you must explicitly opt out with IgnoreQueryFilters when you need to see soft‑deleted rows (e.g., for admin views or audit queries). Manual filtering is explicit but error‑prone; interceptors give flexibility at the cost of added complexity and potential impact on query caching.

Implementation Steps

1. Add the IsDeleted property to your entity.

public class Blog
{
    public int Id { get; set; }
    public string Url { get; set; }
    public bool IsDeleted { get; set; } // soft‑delete flag
}

2. In your DbContext, configure the filter in OnModelCreating.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .HasQueryFilter(b => EF.Property(b, "IsDeleted") == false);
    base.OnModelCreating(modelBuilder);
}

3. Add a migration and update the database.

# Run in the project directory
# Requires .NET SDK and EF Core tools
# No elevated permissions needed; just access to the project files
dotnet ef migrations add AddSoftDeleteToBlog
dotnet ef database update

4. When you need to include soft‑deleted rows, use IgnoreQueryFilters.

var allBlogs = context.Blogs
    .IgnoreQueryFilters()
    .Where(b => b.Url.Contains("example"))
    .ToList();

Validation and Verification

After applying the migration, verify that the filter is active:

1. Insert a normal blog and a soft‑deleted blog.

context.Blogs.Add(new Blog { Url = "normal.com", IsDeleted = false });
context.Blogs.Add(new Blog { Url = "deleted.com", IsDeleted = true });
context.SaveChanges();

2. Query the set without IgnoreQueryFilters and assert the deleted row is absent.

var active = context.Blogs.ToList();
// Expect only the normal blog
Debug.Assert(active.Count == 1 && active[0].Url == "normal.com");

3. Query with IgnoreQueryFilters and assert both rows appear.

var all = context.Blogs.IgnoreQueryFilters().ToList();
Debug.Assert(all.Count == 2);

4. Check the generated SQL contains the filter predicate.

var sql = context.Blogs.ToQueryString();
// Expected pattern: WHERE [b].[IsDeleted] = 0
Debug.Assert(sql.Contains("[IsDeleted] = 0"));

To see the SQL in practice, enable EF Core logging (e.g., LoggerFactory.Create(builder => builder.AddConsole())) and run a simple query; the console output will show the WHERE clause.

Limitations and Practical Check

• The filter applies to all LINQ queries, including lazy‑loaded navigation properties. If a related entity is soft‑deleted, it will be hidden automatically, which may be unexpected in some scenarios.
• Changing the IsDeleted property type (e.g., to nullable bool?) requires updating the filter expression; otherwise a runtime exception can occur.
• Filters defined on a base type are inherited by derived types. Ensure derived entities do not unintentionally inherit a filter that should be omitted.

Practical way to confirm the filter is working as expected: run the validation steps above against multiple providers (SQL Server, SQLite, PostgreSQL) and verify that the generated SQL always contains the IsDeleted predicate and that the query results match the expectations.

Summary

Adopting EF Core Global Query Filters for soft delete centralizes the logic, eliminates per‑query overhead, and works across all supported providers. The trade‑off is the need to explicitly ignore the filter when you must see deleted rows. Manual filtering is simple but error‑prone, while interceptors provide flexibility at the cost of complexity. Follow the implementation and verification steps to ensure the filter behaves correctly in your application.