Query Filters in Entity Framework Core: Applying Global Filters to Queries

Query filters in Entity Framework Core provide a way to apply a global filter to all queries for a given entity. When a query filter is defined, it is automatically applied to all queries for that entity, without the need to specify the filter condition explicitly in each query.

Query filters are especially helpful when you use soft delete in your apps when you remove data, which means you set a property like IsActive or IsDeleted in the database to indicate that an entity is deleted.

To use query filters in Entity Framework Core, you first need to define the filter in the OnModelCreating method of your DbContext class using the HasQueryFilter method. The method takes a lambda expression that represents the filter condition.

Let’s say you have two entities, Student and School, which also have a relationship between them. These entities/models are defined below:

public class School
{
    public int SchoolId { get; set; }
    public string Name { get; set; }

    public List<Student> Students { get; set; }
}

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }

    public School School { get; set; }
}

Based on the code above you can see that there is a one-to-many relationship between School and Student entities, where a School can have multiple students and a student can belong to a single school.

Adding Filter

To define a query filter for the Student entity, you need to override the OnModelCreating method in your DbContext file and write some code.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>().HasQueryFilter(p => p.IsActive);
}

This code defines a query filter for the Student entity in Entity Framework Core. The filter specifies that only active students should be included in queries for the Student entity. Once defined, the filter is automatically applied to all queries that retrieve instances of the Student entity.

For example, if you have a DbSet<Student> property in your DbContext class and you retrieve all students using the ToList method, only active students will be included in the list.

List<Student> activeStudents = context.Students.ToList();

Query filters are handy for applying a shared filter to multiple queries or enforcing a specific condition for security or business purposes. Additionally, they can be combined with other features of Entity Framework Core to achieve more sophisticated filtering and sorting functionality.

Filter In School

The filter that was added in the previous section, was a simple straightforward filter, which was indicating that only the students that are active will be returned. But, let’s try another scenario, lets’s say you want to return only the schools that have at least one student.

For that, you need to add another filter in the OnModelCreating method, which would look like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<School>().HasQueryFilter(st => st.Students.Count > 0);
    modelBuilder.Entity<Student>().HasQueryFilter(p => p.IsActive);
}

The first filter specifies that only schools with at least one student should be included in queries for the School entity.

So, whenever you try to load the Schools from the database using entity framework using a line of code like this:

var filteredSchool = db.Schools.ToList();

you will get only the Schools that have at least one student assigned to them. In the database, this query would be translated to something like the below:

SELECT [s].[SchoolId], [s].[Name]
FROM [Schools] AS [s]
WHERE (
    SELECT COUNT(*)
    FROM [Students] AS [st]
    WHERE [s].[SchoolId] = [st].[SchoolId]) > 0

Disabling Filters

To disable a query filter in Entity Framework Core, you can use the IgnoreQueryFilters method on a DbSet<T> instance. The IgnoreQueryFilters method temporarily disables any query filters defined for the entity type and allows you to retrieve all records without applying the filter.

Example:

// Retrieve all active students
List<Student> activeStudents = context.Students.ToList(); // Only active students are included

// Temporarily disable the IsActive query filter for the Student entity
List<Student> allStudents = context.Students.IgnoreQueryFilters().ToList(); // All students are included

// Re-enable the IsActive query filter for the Student entity
List<Student> activeStudentsAgain = context.Students.ToList(); // Only active students are included again

The IgnoreQueryFilters method above is called on the DbSet<Student> instance to disable the IsActive query filter or any other filter, if it has more than one filter. This allows all records to be retrieved, regardless of whether they are active or not.

Image by Freepik