Soft delete sounds simple: add an IsDeleted column, set it to true instead of deleting, add a WHERE IsDeleted = 0 to every query. In practice, that last step is where teams consistently fail. A new developer writes a query without the filter. A code review misses it. Six months later, deleted records are showing up in customer-facing reports and nobody can explain why.
EF Core's global query filters solve this at the framework level. Register the filter once on the entity type and every LINQ query against that type — including navigation property loads via Include() — automatically excludes soft-deleted rows. You cannot forget to add the filter to a query because the filter is not on the query. It's on the entity.
This article builds the complete pattern: the interface and base class, the global filter registration, a SaveChangesInterceptor that converts hard deletes into soft deletes automatically, partial indexes to keep the filter fast at scale, and an admin bypass for audit views that need to see everything.
Soft delete is a cross-cutting concern, not a per-entity decision. Define an interface so the interceptor and filter registration can target any entity that opts in — without knowing its concrete type at registration time. Pair it with a base class for entities that need both soft delete and standard audit timestamps.
// ── Interface: opt-in contract for soft-deletable entities ────────────────
public interface ISoftDeletable
{
bool IsDeleted { get; set; }
DateTimeOffset? DeletedAt { get; set; }
string? DeletedBy { get; set; } // user ID or service name
}
// ── Base class: combines soft delete with standard audit columns ──────────
// Use this for entities where you want both concerns via inheritance.
// For entities that only need soft delete, implement ISoftDeletable directly.
public abstract class AuditableEntity : ISoftDeletable
{
public int Id { get; set; }
public DateTimeOffset CreatedAt { get; set; }
public string? CreatedBy { get; set; }
public DateTimeOffset? UpdatedAt { get; set; }
public string? UpdatedBy { get; set; }
// ISoftDeletable
public bool IsDeleted { get; set; }
public DateTimeOffset? DeletedAt { get; set; }
public string? DeletedBy { get; set; }
}
// ── Example domain entities using the base class ──────────────────────────
public class Product : AuditableEntity
{
public string Name { get; set; } = "";
public decimal Price { get; set; }
public string CategoryId { get; set; } = "";
public string Sku { get; set; } = "";
public Category Category { get; set; } = null!;
public ICollection<OrderLine> OrderLines { get; set; } = [];
}
public class Category : AuditableEntity
{
public string Name { get; set; } = "";
public ICollection<Product> Products { get; set; } = [];
}
Keeping DeletedBy on the interface alongside DeletedAt is a deliberate choice. In multi-user systems, "when was this deleted?" is rarely the whole story — "who deleted it?" is equally important for support workflows and compliance audits. Storing it on the row is cheaper than querying a separate audit log table for every soft-delete lookup.
Registering the filter in OnModelCreating by reflecting over all entity types that implement ISoftDeletable means you never have to remember to add the filter when a new entity opts in. The moment a class inherits from AuditableEntity or implements ISoftDeletable, it gets the filter automatically.
public class AppDbContext(DbContextOptions<AppDbContext> options)
: DbContext(options)
{
public DbSet<Product> Products { get; set; } = null!;
public DbSet<Category> Categories { get; set; } = null!;
public DbSet<Order> Orders { get; set; } = null!;
public DbSet<OrderLine> OrderLines { get; set; } = null!;
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Apply configurations from separate IEntityTypeConfiguration classes
modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
// ── Register soft delete filter for every ISoftDeletable entity ──
// Iterates all entity types in the model at startup — zero per-entity boilerplate
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
if (!typeof(ISoftDeletable).IsAssignableFrom(entityType.ClrType))
continue;
// Build: e => !((ISoftDeletable)e).IsDeleted
var param = Expression.Parameter(entityType.ClrType, "e");
var prop = Expression.Property(
Expression.Convert(param, typeof(ISoftDeletable)),
nameof(ISoftDeletable.IsDeleted));
var filter = Expression.Lambda(Expression.Not(prop), param);
modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);
}
}
}
// ── Query behaviour — no changes needed in application code ───────────────
// All these queries automatically exclude IsDeleted = true rows:
// Standard LINQ — filter applied by EF Core
var products = await db.Products.Where(p => p.CategoryId == "cat-1").ToListAsync();
// Include() — filter applies to the included navigation too
var orders = await db.Orders
.Include(o => o.Lines.Where(l => l.Price > 0)) // split filter + soft delete
.ToListAsync();
// ── Bypass: admin queries that need to see deleted rows ───────────────────
var allProducts = await db.Products
.IgnoreQueryFilters() // disables ALL global filters on this query
.Where(p => p.IsDeleted)
.OrderByDescending(p => p.DeletedAt)
.ToListAsync();
// ── Restore: un-delete a soft-deleted record ──────────────────────────────
var deleted = await db.Products
.IgnoreQueryFilters()
.FirstOrDefaultAsync(p => p.Id == productId && p.IsDeleted, ct);
if (deleted is not null)
{
deleted.IsDeleted = false;
deleted.DeletedAt = null;
deleted.DeletedBy = null;
await db.SaveChangesAsync(ct);
}
The Expression-based filter registration is the key to making this automatic. Without it, you'd call modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted) for each entity type — easy to forget when adding a new one. The reflection loop removes that failure mode entirely: add the interface, get the filter.
Calling context.Remove(entity) followed by SaveChanges() still executes a SQL DELETE by default. The interceptor intercepts every Delete state entity before EF Core generates the SQL, converts it to an Update, sets the soft delete columns, and lets EF Core generate an UPDATE statement instead. Application code keeps calling Remove() — the interceptor handles the conversion transparently.
using Microsoft.EntityFrameworkCore.Diagnostics;
public class SoftDeleteInterceptor(ICurrentUserService currentUser)
: SaveChangesInterceptor
{
// Sync path
public override InterceptionResult<int> SavingChanges(
DbContextEventData eventData,
InterceptionResult<int> result)
{
ConvertDeletesToSoftDeletes(eventData.Context);
return result;
}
// Async path — override both or only async is called in async SaveChanges
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken cancellationToken = default)
{
ConvertDeletesToSoftDeletes(eventData.Context);
return new ValueTask<InterceptionResult<int>>(result);
}
private void ConvertDeletesToSoftDeletes(DbContext? context)
{
if (context is null) return;
var deletedEntries = context.ChangeTracker
.Entries<ISoftDeletable>()
.Where(e => e.State == EntityState.Deleted)
.ToList(); // materialise before modifying state
foreach (var entry in deletedEntries)
{
// Convert DELETE → UPDATE
entry.State = EntityState.Modified;
entry.Entity.IsDeleted = true;
entry.Entity.DeletedAt = DateTimeOffset.UtcNow;
entry.Entity.DeletedBy = currentUser.UserId; // from ICurrentUserService
// Mark only the soft-delete columns as modified
// Prevents EF Core from emitting an UPDATE for every column
entry.Property(nameof(ISoftDeletable.IsDeleted)).IsModified = true;
entry.Property(nameof(ISoftDeletable.DeletedAt)).IsModified = true;
entry.Property(nameof(ISoftDeletable.DeletedBy)).IsModified = true;
}
}
}
// ── Register in Program.cs ────────────────────────────────────────────────
builder.Services.AddScoped<SoftDeleteInterceptor>();
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
options.UseSqlServer(builder.Configuration["Database:ConnectionString"]);
// Register the interceptor — it's scoped so it can access ICurrentUserService
options.AddInterceptors(sp.GetRequiredService<SoftDeleteInterceptor>());
});
Marking only the three soft-delete columns as modified — rather than letting EF Core mark the full entity as modified — matters at scale. Without it, every soft delete generates an UPDATE that writes every column on the row, increasing write amplification and potentially overwriting concurrent updates to unrelated columns. The three-property approach generates a tight UPDATE Products SET IsDeleted=1, DeletedAt=..., DeletedBy=... WHERE Id=....
Without an index, every query filtered by IsDeleted = false scans the entire table — including the growing graveyard of soft-deleted rows. On a table with 10 million rows where 40% are soft-deleted, that's 4 million rows the database reads and discards on every query. A partial index covers only the non-deleted rows, keeping the index tight and queries fast regardless of how many rows accumulate in the deleted state.
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
public void Configure(EntityTypeBuilder<Product> builder)
{
builder.ToTable("Products");
builder.Property(p => p.Name).HasMaxLength(200).IsRequired();
builder.Property(p => p.Price).HasPrecision(18, 2);
builder.Property(p => p.Sku).HasMaxLength(20).IsRequired();
// ── Partial index: only non-deleted rows ──────────────────────────
// The soft delete query filter generates WHERE IsDeleted = false.
// This index serves that filter directly — the database reads only
// active rows, regardless of how many soft-deleted rows exist.
builder.HasIndex(p => p.IsDeleted)
.HasFilter("IsDeleted = 0") // SQL Server / SQLite syntax
.HasDatabaseName("IX_Products_Active");
// ── Composite partial index: active rows filtered by category ─────
// Covers: db.Products.Where(p => p.CategoryId == catId)
// (soft delete filter applied automatically by EF Core)
builder.HasIndex(p => new { p.CategoryId, p.IsDeleted })
.HasFilter("IsDeleted = 0")
.HasDatabaseName("IX_Products_Category_Active");
// ── Partial unique index: SKU unique among active products only ───
// Without this, a re-created SKU after soft delete fails the constraint.
// With HasFilter, the same SKU can exist in soft-deleted rows.
builder.HasIndex(p => p.Sku)
.IsUnique()
.HasFilter("IsDeleted = 0")
.HasDatabaseName("UIX_Products_Sku_Active");
// ── Audit columns ─────────────────────────────────────────────────
builder.Property(p => p.CreatedAt).IsRequired();
builder.Property(p => p.DeletedAt).IsRequired(false);
builder.Property(p => p.DeletedBy).HasMaxLength(256).IsRequired(false);
}
}
// ── PostgreSQL syntax note ────────────────────────────────────────────────
// HasFilter uses database-native SQL. For PostgreSQL, use:
// .HasFilter("\"IsDeleted\" = false")
// or configure via HasAnnotation for cross-database portability:
// builder.HasIndex(p => p.IsDeleted)
// .HasFilter(isPostgres ? "\"IsDeleted\" = false" : "IsDeleted = 0");
The partial unique index on Sku solves the most common soft delete breakage in production: unique constraint violations when a logically-deleted record prevents re-creation of the same value. A product that was soft-deleted and needs to be re-added with the same SKU will fail the constraint without the HasFilter on the unique index. This is not an edge case — it's a predictable outcome of any soft delete implementation that doesn't address it explicitly.
When a parent entity is soft-deleted, related child entities often need to be soft-deleted too. EF Core's cascade delete configuration handles hard deletes — it doesn't cascade soft deletes because the interceptor converts the parent's delete before EF Core processes cascade rules. Cascade soft delete requires explicit traversal of navigation properties in the interceptor.
private void ConvertDeletesToSoftDeletes(DbContext? context)
{
if (context is null) return;
// Collect all ISoftDeletable entities marked for deletion
var toSoftDelete = context.ChangeTracker
.Entries<ISoftDeletable>()
.Where(e => e.State == EntityState.Deleted)
.ToList();
foreach (var entry in toSoftDelete)
{
entry.State = EntityState.Modified;
entry.Entity.IsDeleted = true;
entry.Entity.DeletedAt = DateTimeOffset.UtcNow;
entry.Entity.DeletedBy = _currentUser.UserId;
entry.Property(nameof(ISoftDeletable.IsDeleted)).IsModified = true;
entry.Property(nameof(ISoftDeletable.DeletedAt)).IsModified = true;
entry.Property(nameof(ISoftDeletable.DeletedBy)).IsModified = true;
// ── Cascade to loaded navigation properties ───────────────────────
// Only cascades to entities already in the change tracker.
// For unloaded navigations, add explicit Include() before deleting
// or handle cascade in the service layer before calling Remove().
foreach (var navigation in entry.Navigations)
{
if (!navigation.IsLoaded) continue;
var childEntities = navigation.CurrentValue switch
{
ISoftDeletable single => [single],
IEnumerable<ISoftDeletable> many => many.ToList(),
_ => []
};
foreach (var child in childEntities)
{
// Skip already-deleted children
if (child.IsDeleted) continue;
child.IsDeleted = true;
child.DeletedAt = DateTimeOffset.UtcNow;
child.DeletedBy = _currentUser.UserId;
// Ensure EF Core tracks the change
context.Entry(child).Property(
nameof(ISoftDeletable.IsDeleted)).IsModified = true;
context.Entry(child).Property(
nameof(ISoftDeletable.DeletedAt)).IsModified = true;
}
}
}
}
// ── Integration test: verify the filter and interceptor work together ─────
public class SoftDeleteTests(WebApplicationFactory<Program> factory)
: IClassFixture<WebApplicationFactory<Program>>
{
[Fact]
public async Task SoftDelete_ExcludesFromDefaultQueries_AndVisibleWithBypass()
{
using var scope = factory.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
// Arrange: seed a product
var product = new Product { Name = "Test Widget", Price = 9.99m, Sku = "TW-0001" };
db.Products.Add(product);
await db.SaveChangesAsync();
var id = product.Id;
// Act: soft delete via Remove()
var toDelete = await db.Products.FindAsync(id);
db.Products.Remove(toDelete!);
await db.SaveChangesAsync();
// Assert: not visible in normal queries (filter applied)
var fromNormal = await db.Products.FirstOrDefaultAsync(p => p.Id == id);
Assert.Null(fromNormal);
// Assert: visible when bypassing filters
var fromBypass = await db.Products
.IgnoreQueryFilters()
.FirstOrDefaultAsync(p => p.Id == id);
Assert.NotNull(fromBypass);
Assert.True(fromBypass!.IsDeleted);
Assert.NotNull(fromBypass.DeletedAt);
}
}
The integration test is the non-negotiable verification step. Unit tests on the interceptor logic are useful but insufficient — they don't catch the case where the query filter registration fails silently (for example, if the expression builder has a type mismatch that EF Core swallows). The integration test exercises the full stack: interceptor, filter registration, database round-trip, and query bypass. Run it in CI on every migration change.
Yes. If the included entity type has its own global query filter, EF Core applies it automatically to the Include() result. Load an Order with its OrderLines and soft-deleted lines are excluded from the collection without any extra code. To load deleted related entities in a specific query, call IgnoreQueryFilters() — this bypasses all global filters on all entity types in that query, not just the root entity.
Yes, if the unique index covers soft-deleted rows. A Users table with a unique index on Email rejects re-registration after a soft delete because the old email row still exists. Fix it with a partial unique index: .HasFilter("IsDeleted = 0") on the index configuration. This allows the same email to exist in soft-deleted rows while enforcing uniqueness only among active records.
Use IgnoreQueryFilters() to load the entity including soft-deleted rows, then call context.Remove(). Update the interceptor to check for a hard-delete flag before converting — for example, a service method that sets a context-scoped marker before calling SaveChanges(). For GDPR erasure, hard delete is the correct mechanism — soft delete alone does not satisfy the right to erasure because the data remains in the database.
Use both. IsDeleted (bool, indexed) is the filter column — fast to compare, simple expression, efficient for partial indexes. DeletedAt (DateTimeOffset?, nullable) provides the timestamp for auditing and data retention policy enforcement. Together they answer "is this deleted?" cheaply on IsDeleted and "when was this deleted?" without a separate audit table. Store DeletedAt in UTC using DateTimeOffset so timezone handling is unambiguous across environments.
Owned entities share the owner's row and don't support independent global query filters — they're filtered through the owner's filter. For table-per-hierarchy (TPH) inheritance, a single filter on the base type covers all derived types automatically because they share one table. For table-per-type (TPT), register the filter on the base entity — EF Core propagates it across the joined subtable queries. Always verify Include() behaviour after any inheritance model change with an integration test.