Entity Framework Core Intermediate

Global query filters and soft delete

A global query filter adds a WHERE condition to every query for an entity. This lesson uses it for the two classic cases, soft delete and multi-tenant apps, and shows how to switch filters off when you really need all rows.

A query with a global filter
  1. StartYour LINQdb.Products.Where(p => p.Price > 100)
  2. Step 2Filter addedAND IsDeleted = 0, every time
  3. Step 3SQL sentDeleted rows never come back
  4. EndNeed them?IgnoreQueryFilters() removes the filter

Soft delete

Instead of deleting rows, mark them as deleted so they can be restored and audited. A query filter hides them everywhere automatically, so no query can forget the check.

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

modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);

// Turn Remove() into a soft delete
public override Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var e in ChangeTracker.Entries<Product>().Where(e => e.State == EntityState.Deleted))
    {
        e.State = EntityState.Modified;
        e.Entity.IsDeleted = true;
        e.Entity.DeletedAt = DateTime.UtcNow;
    }
    return base.SaveChangesAsync(ct);
}

Multi-tenant apps

When one database serves several customers, each row has a TenantId. The filter uses a value from the current DbContext, so each request only sees its own tenant.

C#
public class ShopContext(DbContextOptions<ShopContext> options, ITenantProvider tenant) : DbContext(options)
{
    private readonly int _tenantId = tenant.TenantId;

    protected override void OnModelCreating(ModelBuilder b)
    {
        b.Entity<Order>().HasQueryFilter(o => o.TenantId == _tenantId);
    }
}

Switching filters off and pitfalls

  • IgnoreQueryFilters() on a query returns all rows, for example on an admin restore page.
  • Filters apply to Include too; a required navigation to a filtered row can make the parent disappear. Make such navigations optional or filter both sides.
  • Add the filter column to indexes (for example a filtered index WHERE IsDeleted = 0).
  • Unique constraints still see deleted rows; use a filtered unique index if a deleted name may be reused.
  • EF Core 10 adds named filters so you can switch off one filter and keep others.

Interview question

How would you implement soft delete with EF Core?

Add an IsDeleted flag (and often DeletedAt) to the entity, add a global query filter HasQueryFilter(e => !e.IsDeleted) so normal queries skip deleted rows, and override SaveChanges to turn entities in the Deleted state into Modified with IsDeleted set to true. Use IgnoreQueryFilters() for admin screens that list or restore deleted rows.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.