Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 41 ยท Testing and Patterns

Global Query Filters, Interceptors and Specifications

Apply soft delete and multi-tenancy with global query filters, add auditing with SaveChanges interceptors, and reuse query logic with the specification pattern, with runnable stand-ins.

Some rules apply to every query or every save: hide deleted rows, show a tenant only its own data, stamp CreatedAt and UpdatedBy. Repeating .Where(x => !x.IsDeleted) in a hundred places guarantees that someone forgets it. EF Core has three tools for cross-cutting rules: global query filters, interceptors/SaveChanges overrides, and a hand-built specification pattern for reusable query pieces.

EF Core code is read-only on this site. The runnable blocks are stand-ins for the ideas.

Global query filters

A global query filter is a LINQ predicate EF Core adds to every query for an entity type:

C#
public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

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

Now db.Posts.ToListAsync() becomes SELECT ... FROM Posts WHERE IsDeleted = 0. The filter also applies to Included collections (a blog loaded with its posts never shows deleted posts) and to navigations pointing at a filtered entity.

Soft delete

Soft delete means "mark as deleted instead of removing the row". It keeps history and allows undo. Combine the filter with logic that converts deletes into updates (see interceptors below). To read deleted rows deliberately (an admin screen, a restore feature):

C#
var trash = await db.Posts.IgnoreQueryFilters().Where(p => p.IsDeleted).ToListAsync();
C# Stand-in: a query filter applied to every query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Multi-tenancy

A filter can read a value from the context at query time. This is the standard way to isolate tenants in a shared database:

C#
public class AppDbContext(DbContextOptions<AppDbContext> options, ITenantProvider tenant) : DbContext(options)
{
    private readonly ITenantProvider _tenant = tenant;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Invoice>().HasQueryFilter(i => i.TenantId == _tenant.TenantId);
    }
}

EF Core re-evaluates _tenant.TenantId for each query by parameterizing the access to the context instance's field, so one model serves all tenants safely. Because the model is cached, always reference an instance field or property of the context, never a captured local value. Tenant-aware context pooling also needs care, as described in DbContext in ASP.NET Core and dependency injection.

Filter caveats

  • Required navigations: if a filtered-out parent has non-deleted children, a query through the child may silently drop it (inner joins). EF Core warns about this at model build. Make the matching filter apply to both ends, or make the navigation optional.
  • Cascade deletes ignore filters: deleting a parent can delete hidden children.
  • FromSql and ExecuteUpdate respect or ignore filters differently from LINQ queries; verify each case and test it.
  • Security: a filter is a convenience, not a security boundary. A forgotten IgnoreQueryFilters() or raw SQL bypasses it, so add database-level protection (row-level security) for strong isolation.
  • Since EF Core 10 you can attach several named filters to one entity and disable them individually with IgnoreQueryFilters(["SoftDelete"]), which avoids switching off tenancy when you only want deleted rows.

SaveChanges interceptors and overrides

An interceptor runs your code at points in EF Core's pipeline. The SaveChangesInterceptor is the right place for auditing and for turning deletes into soft deletes, because it sees every change in the tracker before it is written (see change tracking basics).

C#
public interface IAuditable
{
    DateTime CreatedAt { get; set; }
    DateTime? UpdatedAt { get; set; }
}

public class AuditInterceptor(TimeProvider clock) : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, CancellationToken ct = default)
    {
        var db = eventData.Context!;
        var now = clock.GetUtcNow().UtcDateTime;

        foreach (var entry in db.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)    entry.Entity.CreatedAt = now;
            if (entry.State == EntityState.Modified) entry.Entity.UpdatedAt = now;
        }

        foreach (var entry in db.ChangeTracker.Entries<Post>().Where(e => e.State == EntityState.Deleted))
        {
            entry.State = EntityState.Modified;          // turn DELETE into UPDATE
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = now;
        }

        return base.SavingChangesAsync(eventData, result, ct);
    }
}

// registration
builder.Services.AddSingleton<AuditInterceptor>();
builder.Services.AddDbContext<AppDbContext>((sp, o) =>
    o.UseSqlServer(connectionString).AddInterceptors(sp.GetRequiredService<AuditInterceptor>()));

Overriding SaveChanges/SaveChangesAsync on the context does the same job. An interceptor is easier to test and reuse across contexts. Using TimeProvider instead of DateTime.UtcNow lets tests control the clock.

C# Stand-in: an interceptor stamps dates and softens deletes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Other interceptors: DbCommandInterceptor (log or rewrite SQL, add query hints), DbConnectionInterceptor (set a session context or an auth token on connect), and IMaterializationInterceptor (run code when entities are created).

The specification pattern

When the same query conditions appear in several places (an "active premium customers" rule), you can package them as a specification: a small object that describes a filter, includes, ordering and paging, and that a single helper applies to any IQueryable.

C#
public abstract class Specification<T>
{
    public abstract Expression<Func<T, bool>> Criteria { get; }
    public List<Expression<Func<T, object>>> Includes { get; } = new();
    public Expression<Func<T, object>>? OrderBy { get; protected set; }
}

public class ActivePremiumCustomers : Specification<Customer>
{
    public override Expression<Func<Customer, bool>> Criteria =>
        c => c.IsActive && c.Plan == Plan.Premium;
}

public static class SpecificationEvaluator
{
    public static IQueryable<T> Apply<T>(IQueryable<T> query, Specification<T> spec) where T : class
    {
        query = query.Where(spec.Criteria);
        foreach (var inc in spec.Includes) query = query.Include(inc);
        if (spec.OrderBy is not null) query = query.OrderBy(spec.OrderBy);
        return query;
    }
}

var customers = await SpecificationEvaluator.Apply(db.Customers, new ActivePremiumCustomers()).ToListAsync();

The criteria is an expression tree, so EF Core still translates it to SQL. You can also reuse expressions without a framework: keep Expression<Func<Customer,bool>> values in a static class, or write extension methods on IQueryable<Customer> such as .Active().Premium(). Extension methods are the lightest option and compose well. Specifications pay off when you need to combine, test and name business rules, but avoid building a heavy abstraction before you have duplication.

C# Stand-in: composable specifications

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

In real EF Core code the same combination is done on expression trees (so SQL can be generated), commonly with a helper like PredicateBuilder or by chaining Where calls, which ANDs the predicates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does HasQueryFilter(p => !p.IsDeleted) do?

    EF Core appends the filter to every query for the entity, including Include paths.
  2. 2. How do you let an admin screen read soft-deleted rows?

    IgnoreQueryFilters() disables the global filters for that single query.
  3. 3. Where is a good place to set CreatedAt and UpdatedAt automatically?

    The interceptor sees every Added and Modified entry just before the SQL is sent, so no save path can forget it.
  4. 4. Why is a global query filter not a security boundary?

    It is a convenience layer. For strong tenant isolation also use database features like row-level security.

Summary

  • HasQueryFilter applies a predicate to every query: ideal for soft delete and tenancy.
  • Use IgnoreQueryFilters() deliberately; watch required navigations and cascade deletes.
  • SaveChangesInterceptor is the place for auditing and for turning deletes into updates.
  • Specifications (or simple IQueryable extension methods) name and reuse query rules; add them when duplication appears.

Next: The repository and unit of work debate