Now a days, many real world the applications use the concept of soft-delete. 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, most of the queries which read the data from database always need a where clause to fetch only those entities for which IsDeleted property is not true. Only if application has functionality to restore the deleted entities, the query for that functionality would fetch all the deleted entities.

In such cases, where most of the query need same where clause, the application can use the query filters to ensure that all queries issued by data access layer use the same filters.

Let’s see how the query filters can be enabled globally and how they can also be disabled for specific queries.

Demo Overview

We will create a simple application, which has a single context – UniversityContext and a single table – Students. And we will create couple of methods which use global filters while other. And one method which would not use the global filters.

Let’s begin !

Class Library – Context and Entity

Let’s create a .NET core class library – EFCoreGlobalFiltersDemo.Data.EF. Add references to two NuGet packages in that library:

• Microsoft.EntityFrameworkCore.Design – for enabling dotnet CLI EF Core tools on the class library.
• Microsoft.EntityFrameworkCore.SqlServer – as we are going to use SQL Server database

Then add an EF Core model – Student – for holding very basic information related to university students. Note that it has IsDeleted property.

Let’s begin with the UniversityContext given below. It has no additional configurations – just defines a table.

A design time service given below would be helpful to apply migrations and create the database.

Create Database

Then the DOTNET CLI commands given below can be executed to create the database in SQL Server.

Console App

Next, let’s create a console app (EFCoreGlobalFiltersDemo) and add the reference to EFCoreGlobalFiltersDemo.Data.EF class library we created.

It has below methods:

• A method to insert few students, each student has a nationality. From each nationality, we have added a student which is “soft-deleted” (i.e. IsDeleted = true for such students).
• A method to fetch all students (not-deleted) for a given nationality
• A method to fetch all students (not deleted)
• A method to fetch only deleted students

What is the issue in this code ?

If we run the console application, it shows expected results.

• DisplayAllStudents displays all non-deleted records
• DisplayStudentsByNationality displays all non-deleted records who have expected nationality
• DisplayDeletedStudents displays only deleted records.

Then where is the issue ?

The condition for checking if a record is deleted or not was duplicated multiple times. Well, it may not seem to be an issue in this “non-sense” demo.

But imagine, a big application where code is scattered in multiple files. Every file has few queries. Multiple developers are working on those queries. It is very hard (if not impossible) to ensure that a method with name “DisplayAllStudents” would apply the IsDeleted filter.

In real world, every user story would not specify whether the entities are soft-deleted or not. Once the application grows to considerable size, it would not be obvious for anyone to understand if IsDeleted should be used, especially if functionality in question is not related to deletion.

One can say, we can catch such issues in code reviews. But then it would mean we rely on manual verification which takes more time (that’s why it is costly). Also, if we rely on code-reviews, it is also contradicting the concept of SHIFT-LEFT. In case code-reviews are opted for solving this issue, it would not guarantee consistency of the result.

What if we choose to still rely on code reviews, hoping that at least 90-95% times we would be able catch such issues ?

Well, first thing, we cannot guarantee the percentage of consistency achieved via manual efforts. Secondly, by any chance, if the filters like IsDeleted are missed, it would they may have huge impact on performance.

Imagine a table contains thousands of deleted records and only couple of hundreds of non-deleted records. Not having IsDeleted filters would not only break functionality but it would deteriorate the performance of application. Exact amount of delays would depend on many things like amount of data, number of concurrent users, and also on some other design related decisions.

One way to resolve these issues is via global query filters.

Add Global Query Filter

Now, let’s change the UniversityContext and add a global query filter on the entity. In OnModelCreating of context, HasQueryFilter API can be used to specify global query filter.

This would mean that any filter applied via HasQueryFilter would be applied to all LINQ queries which involve the entity type. Whether the entity is directly used or whether it is referenced via a navigation property, this filter would be applicable regardless. Also, if the entity is part of related data which is loaded via Include call, this global query filter would be applicable.

Below code shows modified UniversityContext:

Next, let’s modify console app code and remove all IsDeleted filters. Only in the method which displays deleted records, we will have to use IgnoreQueryFilters call to make query ignore the global filters.

Now if we run the console app , we should see same results as before. But this time, the IsDeleted flag was used in console app only for the method which was related to showing deleted records.

I hope this demo was helpful to understand the concept. Another usage can be in multi-tenant application, where each query should return the data associated with current tenant only.

I hope you find this information helpful. Let me know your thoughts.