It’s one of the eternal debates of software development data management: How do you manage records that get “deleted”?

There are typically 3 approaches. First, there’s the hard delete. When a user or process says a record should get deleted, then it gets removed from the data permanently, gone forever. The only way to recover that data is to go delving through your backups. You do have backups, right?

Second, there’s the archive delete. When a record gets deleted, it gets removed from the place in the data store where it was residing, but a copy of some sort gets stored elsewhere in the data store so it can be restored via some process later should the need arise.

Third, there’s the soft delete. Instead of removing the data from the main data store in any way, a flag gets set on the record indicating that it has been “deleted” and should be ignored. Typically this is either a boolean field or some kind of nullable datetime.

This third option allows for the easiest path to restore deleted records, but it also has the drawback that all queries against that particular data source need to be written to account for those records. If someone forgets to add the equivalent of WHERE DeletedAt IS NULL to their query, the wrong records could easily get included in the dataset being returned.

Enter Entity Framework Core’s global query filters.

Global Query Filters

EF Core provides a feature called global query filters to make this process far easier. Simply put, global query filters are a LINQ predicate that EF Core automatically appends to every query it makes on a particular entity.

The two most common usages for global query filters are soft deletes and multi-tenancy, but it can also be used for many other conditions where you need to filter your datasets by certain conditions by default. For our examples here, we’ll look at the usage for soft deletes.

Setting Up The Soft Delete Pattern

Let’s start with a base entity for our core data. Other core entity classes would inherit from that base class.

public class BaseEntity {
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public string? CreatedBy { get; set; }
    public DateTime? UpdatedAt { get; set; }
    public string? UpdatedBy { get; set; }
    public DateTime? DeletedAt { get; set; }
    public string? DeletedBy { get; set; }
    public bool IsDeleted => DeletedAt is not null;
}

Alternately, we could also do it in the format of an interface that can be applied to any of our entity classes.

public interface ISoftDeletable
{
    DateTime? DeletedAt { get; set; }
    bool IsDeleted => DeletedAt is not null;
}

Either way works fine for our purposes, but the interface approach will make implementing the global query filters slightly easier. With the interface approach, you can do something like the following in the OnModelCreating function in the DbContext.

foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(ISoftDeletable).IsAssignableFrom(entityType.ClrType))
    {
        var param = Expression.Parameter(entityType.ClrType, "e");
        var filter = Expression.Lambda(
            Expression.Equal(
                Expression.Property(param, nameof(ISoftDeletable.DeletedAt)),
                Expression.Constant(null)
            ),
            param
        );
        entityType.SetQueryFilter(filter);
    }
}

This adds to every entity in our model that has the ISoftDeletable interface a default query filter that returns all data records where the DeletedAt field is null, excluding any where the DeletedAt date field has a value.

Whichever approach you use, by defining that as part of the model context, this means that the rest of the code elsewhere doesn’t need to account for deleted records in any of their queries. The filtering gets added automatically by EF Core.

If you use the inherited class approach, you will need to apply the query filter to each of the entities yourself in the OnModelCreating.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>().HasQueryFilter(e => e.DeletedAt == null);
    modelBuilder.Entity<Comment>().HasQueryFilter(e => e.DeletedAt == null);
    modelBuilder.Entity<Tag>().HasQueryFilter(e => e.DeletedAt == null);
}

Making The Soft Delete Work

So, now that we’ve set it up so that we don’t get deleted records returned with our queries, what is the process for marking records as deleted? We don’t want to use the .Remove() function as that will actually delete the record instead of flagging it for a soft delete. So what do we want to do instead? There are a couple of options you might take.

Update

First, you can manually update the record yourself.

db.DeletedAt = DateTime.UtcNow;
db.DeletedBy = user.id;
await db.SaveChangesAsync();

The downside is that you would have to write similar code for every entity that needs the soft delete, leading to a lot of repetition and the potential to miss entities that might need to have a soft delete applied.

Extension Method

Another approach is to add an extension method for DbContext to add a new method.

public static class DbContextExtensions
{
    public static void SoftDelete<T>(this DbContext db, T entity) where T : ISoftDeletable
    {
        entity.DeletedAt = DateTime.UtcNow;
        db.Entry(entity).State = EntityState.Modified;
    }
}

This lets you more easily remember which entities can take the soft delete approach and implement them inline.

db.SoftDelete(post);
await db.SaveChangesAsync();

It does require again that developers remember which entities implement our ISoftDeletable interface, so we should also implement a backstop protection to ensure that our soft delete is enforced, even when a developer forgets to do it “the right way”.

public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<ISoftDeletable>())
    {
        if (entry.State == EntityState.Deleted)
        {
            entry.State = EntityState.Modified;
            entry.Entity.DeletedAt = DateTime.UtcNow;
        }
    }

    return await base.SaveChangesAsync(ct);
}

This piece of code will double check for the ISoftDeletable interface on the object and override it if it wasn’t implemented correctly. This backstop will also let you add a soft delete option to an existing entity without needing to re-write all the other code that may be in the application. At least until you can circle back and clean up the code elsewhere.

Retrieving Soft Deleted Records

There are times when you will need to retrieve records that the global query filter would normally filter out. Perhaps you want to be able to restore a deleted item. This is easily done by adding one thing to your LINQ query: IgnoreQueryFilters().

// Normal query — deleted posts are automatically excluded
var activePosts = await db.Posts
    .Where(p => p.AuthorId == authorId)
    .ToListAsync();

// Opt out of the filter — useful for admin views, audit logs, restore features
var allPosts = await db.Posts
    .IgnoreQueryFilters()
    .Where(p => p.AuthorId == authorId)
    .ToListAsync();

// Restore a soft-deleted record
var post = await db.Posts
    .IgnoreQueryFilters()
    .FirstOrDefaultAsync(p => p.Id == postId);

if (post is not null)
{
    post.DeletedAt = null;
    await db.SaveChangesAsync();
}

Keep in mind that the .IgnoreQueryFilters() function will override all global query filters on that entity. If an entity has multiple global filters applied to it, they all get overridden. So if you’re doing something like soft deletes and multi-tenancy together, you will need to account for that in any query that overrides the filters.

The Hard Delete

What if you eventually do want to hard delete records? Perhaps you have a policy to soft delete for 6 months, then you want the record to go away permanently. The general approach for most implementations that I’ve seen or done myself is to implement a daily or weekly process that runs in the off hours to look at the records and delete those records for good in batches to avoid tying up the system too heavily.

For example, if we’re using SQL Server, I might write an SSIS package or schedule a stored procedure to run on a Monday night to iterate through tables with soft delete records and delete expired records in batches of a couple hundred at a time, depending on the overall need.

Or, you could also implement it in code:

// Hard delete records soft-deleted more than 6 months ago, in batches
public async Task PurgeExpiredRecordsAsync<T>(DbSet<T> dbSet, CancellationToken ct = default)
    where T : class, ISoftDeletable
{
    var cutoff = DateTime.UtcNow.AddMonths(-6);

    var expired = await dbSet
        .IgnoreQueryFilters()
        .Where(e => e.DeletedAt != null && e.DeletedAt < cutoff)
        .Take(200)
        .ToListAsync(ct);

    dbSet.RemoveRange(expired);
    await SaveChangesAsync(ct);
}

// Call the function
await db.PurgeExpiredRecordsAsync(db.Posts);
await db.PurgeExpiredRecordsAsync(db.Comments);

If you’re using EF Core 7 or newer, you can take advantage of the new ExecuteDeleteAsync() function to make it even simpler.

await dbSet
    .IgnoreQueryFilters()
    .Where(e => e.DeletedAt != null && e.DeletedAt < cutoff)  //cutoff defined above
    .ExecuteDeleteAsync(ct);

A Couple Of Big Caveats

There is one critical thing that you need to remember about global query filters. They can only be applied to entities at the root of an inheritance hierarchy. What does that mean? Let’s look at a common pattern.

You have Person base class, from which you derive Student, Teacher, and Parent classes. You can only attach global query filters to the Person base class. That filter then applies to all the derived classes. You cannot create separate query filters for Parent that are different from Student or Teacher. EF Core won’t support it. So keep that in mind if you use class inheritance in your C# code.

The other caveat to remember is that if your LINQ queries join entities together, such as with an Include(), that it joins those records together with INNER JOIN in the database query that gets generated, and any global query filters on any of the involved entities will get applied. This could cause some unexpected results where data you expect to be present in the result set might have gotten filtered out due to your setup.

Other Uses for Global Query Filters - Multi-Tenancy

As I mentioned multi-tenancy previously, it would serve to show a code example of how we might implement this. We’ll start with the setup of our DbContext global query filter.

public class AppDbContext(ICurrentTenantService tenantService) : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>().HasQueryFilter(p => p.TenantId == tenantService.TenantId);
    }
}

This would check the value of the TenantId in some ICurrentTenantService and only return records for that tenant. That service would look something like the following:

public interface ICurrentTenantService
{
    Guid TenantId { get; }
}

public class CurrentTenantService(IHttpContextAccessor httpContext) : ICurrentTenantService
{
    public Guid TenantId =>
        httpContext.HttpContext?.User.FindFirst("tid") is { } claim
            ? Guid.Parse(claim.Value)
            : throw new InvalidOperationException("No tenant in current context.");
}

// Register it
builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ICurrentTenantService, CurrentTenantService>();

If we wanted to combine the two elements in our global query filter, it would look something like this:

modelBuilder.Entity<Post>().HasQueryFilter(p =>
    p.TenantId == tenantService.TenantId &&
    p.DeletedAt == null);

This lets us apply both our soft delete and our multi-tenancy in our global query filter.

And remember, you can apply different global query filters to different entities. Perhaps we want to have a soft delete only on a Comments entity, and a soft delete and multi-tenancy on a Categories entity, and a soft delete, multi-tenancy, and a published/draft flag on a Posts entity, you can do it. Just remember, it’s all or nothing on each entity.

Wrapping It All Up

Global query filters provide an excellent method for applying default filters to queries across all instances of an entity. It can make life simpler, especially for implementing things like soft deletes. One query filter beats writing 50 .Where() clauses on your LINQ queries (and perhaps forgetting them in critical query somewhere).

Global query filters can save your bacon. It’s one of those EF Core features that feel like cheating once you start using them. Add the filter once, forget about it forever. If this saves you some pain, share it with another .NET dev who’s still writing Where(x => x.DeletedOn == null) on every query.