Always will be ready notify the world about expectations as easy as possible: job change page
Apr 11

Global query filters in EF Core

Global query filters in EF Core

Say goodbye to the hassle of having to manually set a default filter for every query

EF Core provides a useful feature called Global Query Filters. It enables you to apply a filter to all queries sent to the database. Two examples of how this feature can be practically used are implementing soft delete and creating a multitenant database.

Building your own query filter

I will assume that you have already established the Database Context class for EF Core.

To add custom query filters, simply modify the OnModelCreating method in the DatabaseContext. Remember, setting query filters only affects specific tables within the database. To filter each table globally, we need to set a query filter on that particular entity.

For instance, the following piece of code creates a global query filter to ignore soft-deleted users:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    // Add a query filter to ignore all soft-deleted users
    modelBuilder.Entity<User>().HasQueryFilter(u => !u.IsDeleted);

Using extension methods

One thing I enjoy doing is developing an extension method that contains the soft delete logic and then implementing it as a query filter.

public static class ModelBuildExtensions
    public static void AddIsDeletedQueryFilter<T>(this ModelBuilder modelBuilder) where T : class, IBaseEntity
        modelBuilder.Entity<T>().HasQueryFilter(u => !u.IsDeleted);

With the use of this extension method, it is now possible to add soft delete global query filters in the following way:

// Add soft-delete global query filter for all of these entities

Ignoring global query filters

Having global query filters is great, but what if you need to view soft-deleted users specifically?

Fortunately, we can bypass global query filters by using the IgnoreQueryFilters() method through chaining.

var softDeletedUsers = _databaseContext.Users
  .IgnoreQueryFilters() // this line does the magic
  .Where(user => user.IsDeleted)

Using multiple query filters

Unfortunately, EF Core does not allow you to define multiple query filters on the same entity.

// This won't work, as an entity can only have one query filter in EF Core
  .HasQueryFilter(u => !u.Age < 18)
  .HasQueryFilter(u => !u.IsDeleted);

At the time of writing this, this is currently an open issue on the EF Core Github page: https://github.com/dotnet/efcore/issues/10275.

But, if your query filter is straightforward, you can combine them into one query filter.

// This will work, as it's only one query filter
  .HasQueryFilter(u => !u.Age < 18 || !u.IsDeleted);
Feb 9, 2021
Author: Mukesh Murugan
In this article, we will learn about Using Entity Framework Core and Dapper in ASP.NET Core together in the same application. Another major point of discussion will be Transactions. By the end of the article, we will have an application...
Jun 8, 2023
Author: Juan Alberto España Garcia
At the end of this article you will understand what “^(?=.*[a-z])(?=.*[A-Z])(?=.*).*” meansIntroduction to C# Regex: why it’s a powerful tool for text manipulationBefore diving into the magical world of regular expressions, let’s get an idea of why using C# Regex...
Mar 18
Author: Erik Pourali
Imagine crafting a library app where users effortlessly find books by title, author, or genre. Traditional search methods drown you in code. But fear not! Dynamic Querying in C# saves the day.In our tale, crafting separate search methods for each...
Mar 10, 2023
Author: Yohan Malshika
Writing unit test code when using the Dapper ORM tool for database operations with ASP.NET Core Web ApplicationHow to write unit tests with DapperWriting unit tests when using the Dapper ORM tool for database operations in an ASP.NET Core Web...
Написать сообщение

© 1999–2024 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET Framework | .NET Core | C# | ASP.NET | Windows Forms | WPF | HTML5 | CSS3 | jQuery | AJAX | Angular | React | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban