Using EF Core Global Query Filters To Ignore Soft Deleted Entities

In a previous post, we talked about how we could soft delete entities by setting up a DateDeleted column (Read that post here : https://dotnetcoretutorials.com/2022/03/16/auto-updating-created-updated-and-deleted-timestamps-in-entity-framework/) But if you’ve ever done this (Or used a simple “IsDeleted” flag), you’ll know that it becomes a bit of a burden to always have the first line of your query go something like this :

dbSet.Where(x => x.DateDeleted == null);

Essentially, you need to remember to always be filtering out rows which have a DateDeleted. Annoying!

Microsoft have a great way to solve this with what’s called “Global Query Filters”. And the documentation even provides an example for how to ignore soft deletes in your code : https://docs.microsoft.com/en-us/ef/core/querying/filters

The problem with this is that it only gives examples on how to do this for each entity, one at a time. If your database has 30 tables, all with a DateDeleted flag, you’re going to have to remember to add the configuration each and every time.

In previous versions of Entity Framework, we could get around this by using “Conventions”. Conventions were a way to apply configuration to a broad set of Entities based on.. well.. conventions. So for example, you could say “If you see an IsDeleted boolean field on an entity, we always want to add a filter for that”. Unfortunately, EF Core does not have conventions (But it may land in EF Core 7). So instead, we have to do things a bit of a rinky dink way.

To do so, we just need to override the OnModelCreating to handle a bit of extra code (Of course we can extract this out to helper methods, but for simplicity I’m showing where it goes in our DBContext).

public class MyContext: DbContext
{
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		foreach (var entityType in modelBuilder.Model.GetEntityTypes())
		{ 
			//If the actual entity is an auditable type. 
			if(typeof(Auditable).IsAssignableFrom(entityType.ClrType))
			{
				//This adds (In a reflection type way), a Global Query Filter
				//https://docs.microsoft.com/en-us/ef/core/querying/filters
				//That always excludes deleted items. You can opt out by using dbSet.IgnoreQueryFilters()
				var parameter = Expression.Parameter(entityType.ClrType, "p");
				var deletedCheck = Expression.Lambda(Expression.Equal(Expression.Property(parameter, "DateDeleted"), Expression.Constant(null)), parameter);
				modelBuilder.Entity(entityType.ClrType).HasQueryFilter(deletedCheck);
			}
		}
		
		base.OnModelCreating(modelBuilder);
	}
}

What does this do?

• Loop through every type that is in our DbContext model
• If the type is inheriting from Auditable class (See previous post here : https://dotnetcoretutorials.com/2022/03/16/auto-updating-created-updated-and-deleted-timestamps-in-entity-framework/)
• Add a global query filter that ensures that DateDeleted is null

Of course, we can use this same loop to add other “Conventions” too. Things like adding an Index to the DateDeleted field is possible via the OnModelCreating override.

Now, whenever we query the database, Entity Framework will automatically filter our soft deleted entities for us!