How To Use Global Query Filters in EF Core

How To Use Global Query Filters in EF Core

3 min read ·

Thank you to our sponsors who keep this newsletter free to the reader:

Delivered straight to your inbox every week, is a newsletter that helps you discover the best micro-startups for sale. Join 3,500+ entrepreneurs and investors looking for their next micro-startup acquisition opportunity and, along the way, learn about how great deals are done.

QuadSpinner Highlighter is an open-source Visual Studio extension that lets you highlight important objects and arbitrary texts to help you navigate your code more easily.

In this week's newsletter, I'll show you how you can remove repetitive conditions in EF Core database queries.

Which kinds of queries fit this description?

An example would be when you implement soft-delete, and have to check if a record was soft-deleted or not in every query.

Also, it's practical if you're working in a multi-tenant system and need to specify a tenantId on every query.

EF Core has a powerful feature that can help you remove repetitive conditions from your code.

I'm talking about Query Filters.

Let's see how we can implement it.

How To Apply Query Filters

Before introducing Query Filters, we will see how the standard approach looks. We have an Orders table that supports soft-deleting. And we never want to return soft-deleted orders.

We'll start with an Order entity that has an IsDeleted property.

public class Order
   public int Id { get; set; }
   public bool IsDeleted { get; set; }

And we have a business requirement that we can only query orders that are not deleted.

Here's what an EF query to get a single Order might look like:

   .Where(order => !order.IsDeleted)
   .Where(order => order.Id == orderId)

This works perfectly for what we need to do.

However, we need to remember to apply this condition every time we want to query the Order entity.

Now, let's see how we can define a Query Filter on the Order entity to apply this check when querying the database.

Inside of the OnModelCreating method on the database context, we need to call the HasQueryFilter method and specify the expression we want:

   .HasQueryFilter(order => !order.IsDeleted);

Now we can omit the soft-delete check from the previous LINQ expression:

   .Where(order => order.Id == orderId)

And this is the SQL that EF will generate with the Query Filter:

FROM Orders o
WHERE o.IsDeleted = FALSE AND o.Id = @orderId

Disabling Query Filters

You may run into a situation where you need to disable Query Filters for a specific query. Luckily, there is an easy way to do this.

In your LINQ expression, you need to call the IgnoreQueryFilters method, and all the Query Filters configured for this entity will be disabled:

   .Where(order => order.Id == orderId)

Be careful when doing this, as you can easily introduce unwanted behavior in your application.

Good Things To Know Before Using Query Filters

Here are a few more details that you should know about Query Filters before using them. Hopefully, this will save you some trouble if you decide to use them in your application.

Configuring multiple Query Filters

Configuring multiple Query Filters on the same entity will only apply the last one. If you need more than one condition, you can do that with the logical AND operator (&&).

Ignoring specific Query Filters

If you need to ignore a specific expression in a Query Filter and leave the rest in place, unfortunately, you can't do that. Only one Query Filter is allowed per entity type.

One solution is calling IgnoreQueryFilters, which will remove the configured Query Filter for that entity type. And then manually apply the condition that you need for that specific query.

Whenever you're ready, there are 4 ways I can help you:

  1. Pragmatic Clean Architecture: Join 2,900+ students in this comprehensive course that will teach you the system I use to ship production-ready applications using Clean Architecture. Learn how to apply the best practices of modern software architecture.
  2. Modular Monolith Architecture: Join 800+ engineers in this in-depth course that will transform the way you build modern systems. You will learn the best practices for applying the Modular Monolith architecture in a real-world scenario.
  3. Patreon Community: Join a community of 1,000+ engineers and software architects. You will also unlock access to the source code I use in my YouTube videos, early access to future videos, and exclusive discounts for my courses.
  4. Promote yourself to 53,000+ subscribers by sponsoring this newsletter.

Become a Better .NET Software Engineer

Join 53,000+ engineers who are improving their skills every Saturday morning.