Entity Framework Global Query Filter: Handle soft delete feature in one place.

Entity framework core is a very feature-rich library, and one of the lesser-known features I want to share with you today is Global Query Filters.

Intro

This was introduced first in EF Core 2.0 

UseCases

Some typical applications of this feature are:

• Soft delete - An Entity Type defines an IsDeleted property.
• Multi-tenancy - An Entity Type defines a TenantId property.

How do I introduce the Global Query Filter for Soft Delete? 

In my career, I used to SoftDelete Database entities for audit purposes in my projects. 

This means the entity is not actually deleted from the database, but it is updated, and a property "IsDeleted" is marked as true.

In such applications, in most of the queries which read the data from the database, I always use a where clause with filter, not deleted values in the Query by checking IsDeleted property is not true. It is a massive headache because I sometimes forget to put in a query.

Below is my sample application proposing the soft delete feature by defining every read query. You see, it is a lazy way to handle every Query. 

Here we are going to examine the SQL statement that the Entity framework has generated and sent to the database. Highlighted condition is generated from the isDelete state.
I used GetTodoItems endpoint. Throughout this blog, I am checking this to generate SQL queries.

You can download starting stage code in git TAG. (StartStatusV1) 

To avoid this robust way now, I habited to use the Global Query Filter feature available in Entity framework core to specify the same condition and going to be applied in all the queries that any reference sends to the database for the entity.

How we use the Global Query Filters 

You can configure query filters to an entity in two ways.

1. in OnModelCreating method of the DbContext.
2. in the EntityTypeConfiguration<T>` class related to entity T.

Here I am defining soft delete filter negation in the DbContext against to TodoItem entity. I used to HasQueryFilter method to register the condition with the entity.

Now we can check again GetTodoItems endpoint SQL query. 

Here we can see IsDelete condition is in the Query.

How do we get rid of the implicitly applied Query Filter?

It is more straightforward than you think. You need to chain a call to the IgnoreQueryFilters() method; what this method is going to do is it going to tell the framework that this Query should not have any query filters applied to it.

Checking again, the GetTodoItems generated SQL query is here.

Notice that in the where statement here, there is no longer the condition that the IsDelete condition ignores from the Query. The Entity Framework is not applying the query filter to this

specific Query. But the other queries are filtered with the query filter. 

Another Usecase

Another usage can be in a multi-tenant application, where each query should return the data associated with the current tenant only.

My thoughts

Most of the time, there are business query patterns that will apply globally to some entities in your application by employing Query Filters of EF Core, you could simply and easily implement the such requirement.

You can find the full code repository below.

https://github.com/csandun/CSandunBlogQueryFilterEfCoreApi

References