Simplify Soft‑Delete Logic with EF Core Global Query Filters

The Problem: Repetitive Soft‑Delete Code

Many applications need a “soft delete” – a flag that marks a row as deleted without physically removing it. A common pattern is to add a boolean IsDeleted column and then remember to add where !IsDeleted to every LINQ query. This leads to duplicated predicates, accidental omissions, and harder-to‑maintain code.

Thesis: Global Query Filters Automate the Filter

EF Core’s Global Query Filters let you attach a single predicate to a type. The filter is automatically appended to every LINQ query that touches that entity, including navigation property traversals. The result is cleaner code and consistent behaviour across the data model.

Configuring a Global Filter

public class BlogContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasQueryFilter(p => !p.IsDeleted);
    }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public bool IsDeleted { get; set; }
}

The lambda p => !p.IsDeleted is translated into SQL WHERE IsDeleted = 0 whenever EF Core builds a query for Post.

A Worked Example

1. Create two posts in a migration or seed method:
   • Post 1: IsDeleted = false
   • Post 2: IsDeleted = true
2. Run the following queries in a console or test:

   var active = context.Posts.ToList();
   var all = context.Posts.IgnoreQueryFilters().ToList();
   

   Result:

   • active contains only Post 1.
   • all contains both posts, because IgnoreQueryFilters() removes the global filter.

When Filters Can Backfire

• Includes: If a related entity also has a filter, the Include will apply that filter too. You may inadvertently exclude related rows you need for reporting.
• Raw SQL: FromSqlRaw or ExecuteSqlRaw bypasses the filter. Use LINQ when soft‑delete logic is required.
• Performance: Each query gets an extra WHERE clause. For very large tables, the cost is negligible compared to the benefits of consistency.

Next Steps

1. Enable EF Core logging (e.g., optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)) and inspect the generated SQL to confirm the IsDeleted predicate is present.
2. Add unit tests that query with and without IgnoreQueryFilters() to ensure the filter behaves as expected.
3. Consider auditing: expose a separate view or DTO that ignores the filter to provide a full history for compliance.
4. Document the pattern in your team’s coding guidelines so new developers know the filter is in place.