Soft Delete in EF Core Using Query Filters

One pattern that shows up almost everywhere in real systems is soft delete.

At first it looks trivial. Just add a column called IsDeleted and set it to true when someone deletes a record.

But after working on several production systems such as payroll systems, payment platforms, internal admin portals, I learned that soft delete is not just a database column. It affects the entire data access layer, especially when using Entity Framework Core.

In this article I want to walk through how I implement soft delete properly using EF Core Query Filters. The examples target .NET 9 and EF Core 9+, but the concept works in earlier versions too.

This is not a theoretical explanation. These are patterns I used in real production systems and also some mistakes I made before fixing the design.

The Real Problem Soft Delete Solves

Let’s start with a simple example.

You have a table:

SQL
Employees
---------
Id
Name
Department

A user deletes an employee record. If we perform a hard delete, the row is permanently removed. In many business systems this is not acceptable.

Examples where I saw this become a problem:

  • HR systems where terminated employees must remain in records.
  • Financial systems where audit requires historical data.
  • Inventory systems where deleted products still appear in old orders.
  • Payment systems where transaction references must never disappear.

Once the row is deleted, there is no easy way to recover it. This is why many systems implement soft delete instead.

Basic Soft Delete Concept

Instead of deleting the row, we mark it as deleted.

Example table:

SQL
Employees
---------
Id
Name
IsDeleted

When deleting:

SQL
UPDATE Employees
SET IsDeleted = 1
WHERE Id = 100;

The row still exists, but the application should treat it as removed.

In EF Core the entity may look like this:

C#
public class Employee
{
    public int Id { get; set; }

    public string Name { get; set; } = default!;

    public bool IsDeleted { get; set; }
}

Now the application must filter queries:

C#
var employees = await _context.Employees
    .Where(e => !e.IsDeleted)
    .ToListAsync();

This works.

But it introduces a serious problem. Someone will forget the filter. I have seen this happen many times.

One developer writes:

C#
_context.Employees.ToListAsync();

Suddenly deleted records appear again.

The fix for this is Global Query Filters.

Global Query Filters in EF Core

EF Core allows applying a filter automatically to every query for an entity. This is done using HasQueryFilter.

Example DbContext:

C#
public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees => Set<Employee>();

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

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

Now every query automatically includes the filter.

Example query:

C#
var employees = await _context.Employees.ToListAsync();

EF Core generates SQL like this:

SQL
SELECT [e].[Id], [e].[Name], [e].[IsDeleted]
FROM [Employees] AS [e]
WHERE [e].[IsDeleted] = 0

This is the first step toward a safe soft delete implementation.

A reusable BaseEntity Approach

In most systems many entities need soft delete.

Instead of repeating the same properties everywhere, I usually create a base entity.

C#
public abstract class BaseEntity
{
    public int Id { get; set; }

    public bool IsDeleted { get; set; }

    public DateTime? DeletedAt { get; set; }

    public string? DeletedBy { get; set; }
}

Then domain entities inherit from it.

C#
public class Employee : BaseEntity
{
    public string Name { get; set; } = default!;
}

This allows us to standardize soft delete behavior across the system. The additional columns (DeletedAt, DeletedBy) are useful for auditing.

Intercepting Deletes with SaveChanges

Another issue I experienced earlier was this:

A developer still used:

C#
_context.Employees.Remove(employee);

EF Core would generate:

C#
DELETE FROM Employees WHERE Id = @p0

That completely bypasses the soft delete logic. The solution is to intercept deletes in SaveChanges()

Example implementation:

C#
public override int SaveChanges()
{
    ApplySoftDelete();
    return base.SaveChanges();
}

public override Task<int> SaveChangesAsync(
    CancellationToken cancellationToken = default)
{
    ApplySoftDelete();
    return base.SaveChangesAsync(cancellationToken);
}

private void ApplySoftDelete()
{
    var entries = ChangeTracker
        .Entries<BaseEntity>()
        .Where(e => e.State == EntityState.Deleted);

    foreach (var entry in entries)
    {
        entry.State = EntityState.Modified;

        entry.Entity.IsDeleted = true;
        entry.Entity.DeletedAt = DateTime.UtcNow;
        entry.Entity.DeletedBy = "system"; // normally current user
    }
}

Now even if someone calls Remove(), EF Core converts it to an update.

Generated SQL:

C#
UPDATE Employees
SET IsDeleted = 1,
    DeletedAt = GETUTCDATE()
WHERE Id = @p0

This makes the soft delete mechanism much harder to bypass.

Ignoring Query Filters (Admin Scenarios)

Sometimes administrators need to view deleted records. EF Core allows bypassing query filters using IgnoreQueryFilters().

Example:

C#
var allEmployees = await _context.Employees
    .IgnoreQueryFilters()
    .ToListAsync();

This is useful for:

  • admin dashboards
  • restore operations
  • audit investigation

Example restore logic:

C#
var employee = await _context.Employees
    .IgnoreQueryFilters()
    .FirstOrDefaultAsync(e => e.Id == id);

if (employee != null)
{
    employee.IsDeleted = false;
    employee.DeletedAt = null;
    employee.DeletedBy = null;

    await _context.SaveChangesAsync();
}

Multi-Tenant + Soft Delete

In multi-tenant systems the filter usually combines TenantId and IsDeleted

Example base entity:

C#
public abstract class BaseEntity
{
    public int Id { get; set; }

    public Guid TenantId { get; set; }

    public bool IsDeleted { get; set; }
}

Then the query filter becomes:

C#
modelBuilder.Entity<Employee>()
    .HasQueryFilter(e =>
        !e.IsDeleted &&
        e.TenantId == _currentTenantId);

Generated SQL looks like:

C#
SELECT *
FROM Employees
WHERE IsDeleted = 0
AND TenantId = @tenantId

This ensures:

  • deleted records are hidden
  • tenants cannot access each other’s data

Performance Considerations

One thing I learned later was that soft delete can impact performance. If a table contains millions of rows, filtering IsDeleted in every query can become expensive.

Proper indexing is important.

Example index:

SQL
CREATE INDEX IX_Employees_IsDeleted
ON Employees(IsDeleted);

In multi-tenant systems I usually create a composite index:

SQL
CREATE INDEX IX_Employees_TenantId_IsDeleted
ON Employees(TenantId, IsDeleted);

For read-heavy systems a filtered index can also help:

SQL
CREATE INDEX IX_Employees_Active
ON Employees(TenantId)
WHERE IsDeleted = 0;

This reduces scanning of deleted rows.

Handling Related Entities

One tricky situations happens with relationships.

Example:

SQL
Department
Employees

If you soft delete a department, what should happen to employees?

Possible strategies:

  • Prevent deletion if children exist
  • Cascade soft delete
  • Leave children untouched

EF Core does not cascade soft delete automatically. In some systems I implemented cascade manually inside SaveChanges(). But in most business systems we prevent deletion if dependent records exist.

Soft Delete vs Temporal Tables

SQL Server also offer temporal tables. Temporal tables automatically keep historical versions of rows.

Comparison:

Soft Delete

  • Implemented in application
  • Easy to restore deleted data
  • Simple queries
  • Works with any database provider

Temporal Tables

  • Implemented in database
  • Full history of changes
  • More complex queries
  • SQL Server specific

In some high audit environments I actually combine both. Soft delete hides records from normal queries, while temporal tables preserve the full change history.

Shadow Property Alternative

EF Core also supports shadow properties. This means the entity does not contain the IsDeleted property.

Example:

C#
modelBuilder.Entity<Employee>()
    .Property<bool>("IsDeleted");

Query filter:

C#
modelBuilder.Entity<Employee>()
    .HasQueryFilter(e =>
        EF.Property<bool>(e, "IsDeleted") == false);

Personally I do not prefer this approach. It hides an important domain behavior from the entity model, which can confuse developers. For most systems I still prefer explicit properties.

Migration Strategy for Existing Tables

If the system already has production data, soft delete must be introduced carefully.

Typical steps:

  • Add Is Deleted column with default value.
  • Add DeletedAt and DeletedBy.
  • Create indexes.
  • Deploy application update with query filters
  • Monitor queries and reports.

Rolling this out gradually avoids unexpected behavior.

Mistakes I Made Before

Looking back at older systems I worked on, I made several mistakes:

  • Forgot to index IsDeleted
  • Forgot to override SaveChanges
  • Used IgnoreQueryFilters() in repository accidentally
  • Soft deleted parent entities without considering child records

These issues usually appear only in production after the data grows.

Final Thoughts

Soft delete is easy to implement poorly. But when implemented correctly with EF Core Query Filters, it becomes a very clean and reliable pattern.

My typical setup today includes:

  • BaseEntity with soft delete fields
  • Global query filters
  • SaveChanges() interception
  • Proper indexing
  • Audit columns (DeletedAt, DeletedBy)

Once this foundation is in place, the rest of the application becomes much safer. Developers can write normal queries without constantly remembering to filter deleted records.

And from my experience working on enterprise systems, that small architecture decision saves a log of headaches later.

Assi Arai
Assi Arai
Articles: 54

Related Posts

Trending now

WordPress Functions
How to Set Up a WordPress Website with Docker Compose
How to Use Microsoft.Extensions.Polly for Resilient HTTP Requests in .NET
LINQ: The Basics and Core Concepts