Implementing Soft Delete in EF Core: Global Query Filters vs. Manual Filtering

The Soft Delete Dilemma

In many production systems, permanently deleting data is risky. A "soft delete" approach marks records as deleted using a boolean flag (e.g., IsDeleted) rather than removing the row from the database. The primary engineering challenge is ensuring that these marked records do not appear in standard application queries while remaining accessible for administrative recovery or auditing.

The decision is whether to automate this exclusion using EF Core Global Query Filters or to handle it explicitly via manual Where clauses. The wrong choice leads to either "leaking" deleted data to users or creating an unmaintainable codebase where every single query requires a repetitive filter.

Comparing Implementation Strategies

The following table compares the two primary methods for handling soft deletes in EF Core (tested on EF Core 6.0+).

Feature	Global Query Filters (HasQueryFilter)	Manual Filtering (Where)
Consistency	Automatic; applied to all queries by default.	Manual; relies on developer discipline.
Visibility	Implicit; hidden from the LINQ query.	Explicit; clearly visible in the code.
Bypass Method	.IgnoreQueryFilters()	Omit the Where clause.
Performance	Negligible overhead on plan cache.	No overhead beyond the SQL WHERE.
Risk	Accidental filtering of admin views.	Accidental exposure of deleted data.

Trade-offs and Constraints

Global Query Filters

Global filters are configured in the OnModelCreating method. They are ideal for multi-tenant applications or systems where deleted data should never be seen by the end-user. However, they apply to all queries for that entity, including those involving navigation properties (joins). If you have a complex hierarchy, a filter on a parent entity will automatically filter out its children in a Include statement.

Manual Filtering

Manual filtering is preferred when the "deleted" state is contextual. For example, if a record is "deleted" for a customer but "archived" for an accountant, a global filter is too blunt. To avoid repetition, developers typically implement this via IQueryable extension methods.

Concrete Implementation: Global Query Filters

To implement a scalable soft delete system, use an interface to ensure consistency across multiple entities.

// 1. Define a marker interface
public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
}

// 2. Apply to your entity
public class Product : ISoftDeletable
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public bool IsDeleted { get; set; }
}

// 3. Configure the filter in DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // This applies the filter to all entities implementing ISoftDeletable
    foreach (var entityType in modelBuilder.Model.GetEntityTypes()
        .Where(e => typeof(ISoftDeletable).IsAssignableFrom(e.ClrType)))
    {
        modelBuilder.Entity(entityType.ClrType).HasQueryFilter(ConvertFilterExpression(entityType.ClrType));
    }
}

// Helper to create the lambda expression: e => !e.IsDeleted
private static LambdaExpression ConvertFilterExpression(Type type)
{
    var parameter = Expression.Parameter(type, "e");
    var property = Expression.Property(parameter, nameof(ISoftDeletable.IsDeleted));
    var notExpression = Expression.Not(property);
    return Expression.Lambda(notExpression, parameter);
}

Operating the Filter

Run these commands within your service layer. Ensure the executing user has the appropriate permissions to bypass filters.

• Standard Query: context.Products.ToList(); — Generates SELECT ... WHERE IsDeleted = 0.
• Admin Query: context.Products.IgnoreQueryFilters().ToList(); — Generates SELECT ... without the IsDeleted check.

Critical Limitations and Verification

The Migration Trap

When adding IsDeleted to an existing table via migrations, you must provide a default value. If the column is added as nullable or without a default, existing rows may contain NULL, which may be evaluated as true or false depending on the SQL provider, potentially hiding all existing data immediately after the update.

Client-Side Evaluation Risk

If you call .AsEnumerable() or .ToList() before applying further logic, the query filter is applied at the database level. However, if you use raw SQL via FromSqlRaw, the global filter is not automatically appended to the SQL string. You must manually add the WHERE` clause or chain .IgnoreQueryFilters() to be explicit.

Verification Steps

1. SQL Log Check: Enable logging using optionsBuilder.LogTo(Console.WriteLine). Execute a standard query and verify the generated SQL contains WHERE [p].[IsDeleted] = 0.
2. State Test: Insert one record with IsDeleted = true and one with false. A standard DbSet.Count() should return 1.
3. Bypass Test: Execute the same count with .IgnoreQueryFilters(); it should return 2.

Rollback

To remove the soft delete behavior, remove the HasQueryFilter configuration from OnModelCreating and create a migration to drop the IsDeleted column from the database schema.