Three months into a new multi-tenant SaaS project, we shipped a simple delete feature. Hours later, our support channels lit up. Customers were confused—they’d delete an invoice, but it would mysteriously reappear in their quarterly reports.
The bug was a classic. A single, forgotten Where clause. A reporting query, written by someone new to the domain, didn’t include our manual !IsDeleted filter. Boom. Soft-deleted records were leaking straight into the UI.
That incident taught me a hard lesson: manual soft deletes are a ticking time bomb. EF Core’s global query filters are the only way I’ll implement them now. They’re not just a convenience; they’re a safety net.
The ‘Just Add an IsDeleted Flag’ Trap
Everyone starts here. You add an IsDeleted property to your entity and tell the team, “Just remember to filter it out.” It works, for a while.
// This works... until it doesn't.
var activeUsers = context.Users
.Where(u => !u.IsDeleted)
.ToList();
// Oops. A developer writing a new report forgets the filter.
var userReport = context.Users
.Include(u => u.Orders)
.ToList(); // Deleted users are back from the dead.
In a growing codebase with multiple developers, someone will forget the Where clause. It’s inevitable. In our case, it wasn’t just a weird bug; it broke customer trust. They thought our software was unreliable.
EF Core’s Global Query Filters to the Rescue
This is where you stop relying on memory and start enforcing the rule at the DbContext level. You define the filter once in OnModelCreating, and EF Core applies it to every single LINQ query for that entity. Automatically.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>()
.HasQueryFilter(u => !u.IsDeleted);
modelBuilder.Entity<Order>()
.HasQueryFilter(o => !o.IsDeleted);
}
With this in place, those previous queries now behave correctly without any changes.
// Both queries now automatically exclude deleted records. No code change needed.
var activeUsers = context.Users.ToList();
var userWithOrders = context.Users.Include(u => u.Orders).ToList();
The real magic is that it even works on navigation properties. When you load a user and Include(u => u.Orders), EF Core automatically adds the !o.IsDeleted filter to the join. Set it and forget it.
How I Set Up Soft Deletes in New Projects
Here’s the boilerplate I use to build this out in a scalable way.
Step 1: A Base Entity for Everything
First, I create a base class that all my main entities will inherit. This keeps things consistent.
public abstract class AuditableEntity
{
public int Id { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime? UpdatedAt { get; set; }
public bool IsDeleted { get; set; }
public DateTime? DeletedAt { get; set; }
}
public class User : AuditableEntity
{
public string Email { get; set; }
public string Name { get; set; }
public List<Order> Orders { get; set; } = new();
}
I don’t recommend this unless you really need it: Some people argue against base entities. For me, the consistency of having audit fields on every table is worth it, especially for soft deletes and tenancy.
Pro Tip: I always include a
DeletedAttimestamp. When a customer asks, “What happened to invoice #123?”, being able to give them an exact deletion timestamp is a lifesaver for debugging and auditing.
Step 2: Apply the Filter Automatically
Instead of adding HasQueryFilter for every single entity, you can use a little reflection to apply it to everything that inherits from AuditableEntity.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Find all types that inherit from AuditableEntity and apply the filter
foreach (var entityType in modelBuilder.Model.GetEntityTypes()
.Where(e => typeof(AuditableEntity).IsAssignableFrom(e.ClrType)))
{
var parameter = Expression.Parameter(entityType.ClrType, "e");
var property = Expression.Property(parameter, nameof(AuditableEntity.IsDeleted));
var condition = Expression.Equal(property, Expression.Constant(false));
var lambda = Expression.Lambda(condition, parameter);
modelBuilder.Entity(entityType.ClrType).HasQueryFilter(lambda);
}
}
This is a clean, DRY (Don’t Repeat Yourself) way to handle it. Now any new entity that needs soft-delete capability just has to inherit from AuditableEntity.
Step 3: Combine with Multi-Tenancy
For multi-tenant apps, this gets even more powerful. You can chain the soft-delete filter with your tenant filter.
// In your DbContext, assuming you have a _currentTenantId field
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>()
.HasQueryFilter(u => !u.IsDeleted && u.TenantId == _currentTenantId);
}
Now, you’ve got automatic data isolation and soft-delete protection in one shot. This is huge for preventing both security issues and bugs.
When You Need to See the Ghosts: Bypassing Filters
Sometimes, you need to see the “deleted” data. An admin dashboard, a data recovery tool, or an audit log are common examples. For that, EF Core gives you IgnoreQueryFilters().
public async Task<List<User>> GetAllUsersIncludingDeletedAsync()
{
// This temporarily disables the HasQueryFilter for this query only
return await _context.Users
.IgnoreQueryFilters()
.ToListAsync();
}
public async Task<User> GetJustOneDeletedUserAsync(int userId)
{
return await _context.Users
.IgnoreQueryFilters()
.FirstOrDefaultAsync(u => u.Id == userId && u.IsDeleted);
}
Use this method with caution. It’s a backdoor for a reason. I typically restrict its usage to specific admin-level services and repositories.
Where This Can Bite You: The Gotchas
Global query filters are great, but they aren’t foolproof. Here are a few things that have burned me in production.
1. Index your IsDeleted column.
When your tables get big, that extra WHERE IsDeleted = 0 clause can slow things down if the column isn’t indexed. Most of your data will have IsDeleted = 0, so a standard index isn’t always effective. A filtered index is usually the way to go.
-- SQL Server syntax
CREATE NONCLUSTERED INDEX IX_Users_IsDeleted_Filtered
ON Users (Id, Email) -- Include columns you query on
WHERE (IsDeleted = 0);
2. Raw SQL doesn’t care about your filters. This one is sneaky. Global query filters only apply to LINQ queries. If you drop down to raw SQL, you’re on your own.
// DANGER: This WILL return soft-deleted records.
var users = context.Users
.FromSqlRaw("SELECT * FROM Users")
.ToList();
// You have to add the filter manually.
var activeUsers = context.Users
.FromSqlRaw("SELECT * FROM Users WHERE IsDeleted = 0")
.ToList();
3. Cascading deletes need to be manual.
If deleting a User should also soft-delete their Orders, you have to code that logic yourself. The query filter only prevents you from seeing deleted data; it doesn’t change your C# write logic.
public async Task SoftDeleteUserAndTheirOrdersAsync(int userId)
{
var user = await _context.Users
.Include(u => u.Orders)
.FirstOrDefaultAsync(u => u.Id == userId);
if (user != null)
{
user.IsDeleted = true;
user.DeletedAt = DateTime.UtcNow;
foreach (var order in user.Orders)
{
order.IsDeleted = true;
order.DeletedAt = DateTime.UtcNow;
}
await _context.SaveChangesAsync();
}
}
So, When Should You Use It?
Here’s my final take.
Use global query filters when:
- You are implementing soft deletes, multi-tenancy, or any other cross-cutting data rule.
- You have more than one developer working on the codebase.
- Data integrity is critical, and you can’t afford to accidentally leak filtered-out data.
Avoid it or be careful when:
- Your application logic frequently needs to interact with “deleted” data. Constantly using
IgnoreQueryFilters()can be a code smell. - You have extremely complex, performance-sensitive queries. While usually fine, the automatically applied
WHEREclause can sometimes lead to suboptimal query plans. Always profile. - You rely heavily on raw SQL queries, as the filters won’t apply.
For me, the safety and consistency they provide are non-negotiable for any application that uses soft deletes. They turn a common source of bugs into a solved problem, letting the team focus on building features instead of hunting down forgotten Where clauses.