In this article, we will discuss Entity Framework Core’s global query filters and how to use them. We will also consider other scenarios related to using query filters that we need to be aware of.
Let’s dive in.
What Is a Global Query Filter?
Global query filters are a handy feature of Entity Framework (EF) Core that enables us to apply a WHERE condition to all queries on a given entity type. These filters are defined at the model level, so we can keep our regular queries separate from our always-applied filters. But when would we want this behavior?
Global Query Filter Use Cases
One reason to use global query filters is for row-level security. For example, we might store entities in a table and want to restrict users to seeing only entities they create. In this scenario, we can apply a global query filter using the current user’s ID as a parameter and compare it against the entity’s “created by” property.
A similar concept is that of multitenancy. If we want to ensure that every user can only access data in their tenant, we can use the tenant ID as the parameter for the global query filter.
Finally, and probably the most common use case for a global query filter is implementing soft delete. This may be for performance reasons, for record-keeping, or for restoration. When using soft delete, we mark an entity as deleted instead of actually removing it from the database. Since the entity remains in the database, we need to filter out the soft deleted records when retrieving active records.
Let’s see an implementation of this last use case.
Implementing a Global Query Filter
For simplicity, let’s start by setting up EF Core in a console application:
md GlobalQueryFilters cd GlobalQueryFilters dotnet new console
Now let’s install the necessary NuGet packages:
dotnet add package Microsoft.EntityFrameworkCore dotnet add package Microsoft.EntityFrameworkCore.InMemory
In this article, we will use EF Core’s in-memory database as data storage, but we could use any EF Core-supported database.
Next, let’s create the DbContext:
public sealed class SoftDeleteDbContext(DbContextOptions<SoftDeleteDbContext> options) : DbContext(options)
{
}
For now, we leave it empty. In the next section, we will create our entity and populate the DbContext.
Finally, in the Program class, let’s register our DbContext:
var services = new ServiceCollection();
services.AddDbContext<SoftDeleteDbContext>(options => options.UseInMemoryDatabase("GlobalQueryFilters"));
var serviceProvider = services.BuildServiceProvider();
Here, we’re creating a dependency injection container, registering the DbContext, and then building our ServiceProvider.
Setting Up the Global Query Filter
Now let’s create a simple entity with an Id and an IsDeleted flag:
public sealed class SoftDeleteEntity
{
public Guid Id { get; private set; }
public bool IsDeleted { get; set; }
}
Then let’s override the OnModelCreating() method on the DbContext:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SoftDeleteEntity>().HasKey(s => s.Id);
modelBuilder.Entity<SoftDeleteEntity>().Property(s => s.IsDeleted).IsRequired();
modelBuilder.Entity<SoftDeleteEntity>().HasQueryFilter(entity => !entity.IsDeleted);
}
Here, we configure the Id property as the key and then mark the IsDeleted property as required. Lastly, we add our global query filter, which returns true only if the entity being queried has not been deleted.
Since we’re using the in-memory database, we don’t have to create a migration, but we should remember to add one as needed for other databases.
Now we are ready to test our query filter.
Testing the Global Query Filter
Let’s return to the Program class and add this testing code after the call to BuildServiceProvider():
var dbContext = serviceProvider.GetRequiredService<SoftDeleteDbContext>();
var entity = new SoftDeleteEntity();
dbContext.SoftDeleteEntities.Add(entity);
await dbContext.SaveChangesAsync();
Console.WriteLine($"New entity added with id: {entity.Id}");
var entityToDelete = await dbContext.SoftDeleteEntities
.FirstOrDefaultAsync(e => e.Id == entity.Id);
Console.WriteLine($"Entity queried from DB: {entityToDelete!.Id}");
entityToDelete.IsDeleted = true;
await dbContext.SaveChangesAsync();
Console.WriteLine("Entity soft deleted");
var entityAfterDelete = await dbContext.SoftDeleteEntities.FirstOrDefaultAsync(e => e.Id == entity.Id);
Console.WriteLine($"Entity queried from DB after soft delete: {entityAfterDelete?.Id.ToString() ?? "null"}");
To understand this code better, let’s run it and then inspect it along with its output:
New entity added with id: b56e23a7-3333-4397-3acf-08dcfbf3bcbf Entity queried from DB: b56e23a7-3333-4397-3acf-08dcfbf3bcbf Entity soft deleted Entity queried from DB after soft delete: null
First, we request the DbContext from the dependency injection container. Then we create a new SoftDeleteEntity, add it to the context, and commit the changes. Next, we query the entity by its ID to ensure it exists.
By default the IsDeleted property is false, so our query filter shouldn’t filter the newly added entity. However, it is important to note that our global query filter is already running on our first FirstOrDefaultAsync() call. As the first two lines of output show, the entity was saved and retrieved.
Next, we soft delete the entity by setting IsDeleted to true and persisting the changes. Finally, we execute our query again, and the last line of output shows that the global query filter excluded the soft-deleted entity.
Now that we have seen how global query filters work, let’s review some important information to keep in mind when using them.
Disabling Global Query Filters
Sometimes we want to opt out of using a global query filter for a specific query. For example, when we would like to display deleted entities for restoration purposes. Fortunately, EF Core provides a convenient way to disable the query filter, by simply calling the IgnoreQueryFilters() extension method in the LINQ tree.
Let’s add a new query to the end of the Program class:
var entityAfterDeleteWithDisabledQueryFilter = await dbContext.SoftDeleteEntities
.IgnoreQueryFilters()
.FirstOrDefaultAsync(e => e.Id == entity.Id);
Console.WriteLine("Entity queried from DB after soft delete with disabled query filter: " +
entityAfterDeleteWithDisabledQueryFilter?.Id.ToString() ?? "null");
Now, let’s rerun the application and inspect the output:
New entity added with id: 04a64c96-48a2-41f8-0a53-08dcfd79062d Entity queried from DB: 04a64c96-48a2-41f8-0a53-08dcfd79062d Entity soft deleted Entity queried from DB after soft delete: null Entity queried from DB after soft delete with disabled query filter: 04a64c96-48a2-41f8-0a53-08dcfd79062d
Instead of null, the output is the entity’s Id. This shows that we successfully disabled the query filter.
Filtered Required Navigation Properties
There is a significant pitfall to be aware of when using global query filters. If we define a required navigation property on our entity and a query filter filters that navigation but not the parent entity, then the result will still exclude the parent entity.
Let’s implement an example to understand this behavior.
We will start with a new entity called ChildEntity:
public sealed class ChildEntity
{
public Guid Id { get; private set; }
public ParentEntity Parent { get; private set; } = null!;
}
Next, let’s create a new ParentEntity that has a required navigation to its children:
public sealed class ParentEntity(IEnumerable<ChildEntity> children)
{
public Guid Id { get; private set; }
public bool IsDeleted { get; set; }
public IEnumerable<ChildEntity> Children { get; private set; } = children;
private ParentEntity() : this(null!){} // ctor for EF
}
After that, we will configure this relationship in a new DbContext:
public sealed class NavPropDbContext(DbContextOptions<NavPropDbContext> options) : DbContext(options)
{
public DbSet<ChildEntity> Children { get; private set; } = null!;
public DbSet<ParentEntity> ParentEntities { get; private set; } = null!;
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ParentEntity>().HasKey(s => s.Id);
modelBuilder.Entity<ParentEntity>().Property(s => s.IsDeleted).IsRequired();
modelBuilder.Entity<ParentEntity>().HasQueryFilter(entity => !entity.IsDeleted);
modelBuilder.Entity<ParentEntity>().HasMany(s => s.Children).WithOne(c => c.Parent).IsRequired();
modelBuilder.Entity<ChildEntity>().HasKey(c => c.Id);
}
}
We add a Children DbSet and configure the relationship between parent and child as a simple many-to-one required relationship. When a relationship is marked as required, EF Core uses an INNER JOIN when querying the database.
The Program Class Modification
Now let’s modify our Program class to register the new DbContext immediately under the previous one:
services.AddDbContext<NavPropDbContext>(options => options.UseInMemoryDatabase("GlobalQueryFilters"));
Finally, let’s add this code to the bottom of the Program class:
var navPropDbContext = serviceProvider.GetRequiredService<NavPropDbContext>();
var child = new ChildEntity();
var parent = new ParentEntity([child]);
navPropDbContext.ParentEntities.Add(parent);
await navPropDbContext.SaveChangesAsync();
Console.WriteLine($"New parent added with id: {parent.Id}");
Console.WriteLine($"New child added with id: {child.Id}");
parent.IsDeleted = true;
await navPropDbContext.SaveChangesAsync();
Console.WriteLine("Parent soft deleted");
var childAfterParentDelete = await navPropDbContext.Children
.Include(c => c.Parent)
.FirstOrDefaultAsync(c => c.Id == child.Id);
Console.WriteLine($"Child queried after parent is soft deleted: {childAfterParentDelete?.Id.ToString() ?? "null"}");
We create a child for our entity, then soft delete the parent and query the created child.
Now with everything set up, let’s see the output of our app:
New parent added with id: 7cd9f915-cc09-4231-afa5-08dcfd884154 New child added with id: d5a42220-3217-461e-ed5d-08dcfd884159 Parent soft deleted Child queried from DB after parent is soft deleted: null
Since we previously marked the relationship as required, and then filtered the parent out by soft deleting it, the query did not return any children, due to the INNER JOIN.
So it is important to consider relationships when declaring global query filters. The best solution for this problem is to create a global query filter for children too, with their parent’s filter condition:
modelBuilder.Entity<ChildEntity>().HasQueryFilter(c => !c.Parent.IsDeleted);
This way, we clearly indicate our intention. If a parent is filtered out by its query filter, we don’t want the children either. Or, if we need the children regardless of their parent’s status, we can always ignore the query filter, as we saw earlier. Finally, it’s also possible to configure the relationship as optional instead of required. In this case, EF Core will use a LEFT JOIN rather than an INNER JOIN when querying, so children will always be returned.
Conclusion
In this article, we explored EF Core’s global query filter feature. We discussed what it is, and when we might use it, and created an example implementation for soft deleting entities. Finally, we discussed other important considerations when using global query filters and how to mitigate potential issues.
