How to dynamically build OR filters in an Entity Framework Core LINQ query

Entity Framework Core is an ORM (Object Relational Mapper) that allows us to leverage LINQ (Language Integrated Query) to abstract away the SQL (Structured Query Language) that would otherwise need to be written for interactions with a database.

However, while LINQ is an awesome .NET feature, there are some limitations to be aware of when it comes to building up queries. One of these limitations comes to light when trying to dynamically build ‘OR’ filters.

By default, when you are building a LINQ query by appending separate Where methods to an IQueryable object, this results in ‘AND’ logic being applied. Usually, this is fine, however, there are occasions where OR logic is required and the query needs to be built dynamically as the full list of filters that need to be applied are not known at compile-time.

In this article, I will show how you can dynamically build OR filters in your LINQ query without needing to add any third-party packages to your solution.

Setup

First, let’s set up a quick environment for testing things out. For convenience, we’ll work with the same Entities and DbContext used in my previous How to catch and parse a SQL Server Duplicate Key Exception article.

If you’d like to follow along, I recommend creating a C# Console App using Visual Studio or Visual Studio Code and ideally targeting the latest version of .NET which is 8.0 at the time of writing.

Entities

In the Console App, let’s assume we are developing a simple ‘Todo’ application that uses the following types/entities.

public enum TodoStatus
{
    New = 0,
    InProgress = 1,
    Completed = 2
}
 
public class Todo
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Title { get; set; } = "";
    public TodoStatus Status { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
}

Create the above types in your project if you are following along.

DbContext

Next, let’s create a DbContext class that contains a DbSet for the previously listed entities, as follows.

using Microsoft.EntityFrameworkCore;
 
public class TodoDbContext : DbContext
{
    public DbSet<Todo> Todos { get; set; }
    public DbSet<User> Users { get; set; }
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TodosDb;Trusted_Connection=True;");
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Todo>().Property(t => t.Title).HasMaxLength(100);
        modelBuilder.Entity<User>().HasIndex(u => u.Email).IsUnique();
    }
}

Note that in a real-world application, the connection string should come from a configuration source. Additionally, I am leaving out project namespaces from the code extracts shown in this article for the sake of simplicity.

Querying

Now that we have set up our Entities and DbContext, let’s proceed to query some data using LINQ.

What we want to achieve is as follows.

Given a set of search terms that are supplied by the user, split the terms on the space character and return all todos that contain any of the individual terms in the title of the todo record.

Let’s attempt to implement the above requirements in the following subsections.

Default chaining behaviour

Below is the first attempt to meet the requirements laid out above. Add the code to the ‘Program.cs’ file of your Console App project if you are following along.

using Microsoft.EntityFrameworkCore;
 
using var context = new TodoDbContext();
 
bool isNewDatabase = context.Database.EnsureCreated();
 
if (isNewDatabase)
{
    await context.AddRangeAsync(new User
    {
        Email = "test@example.com",
        Todos = new List<Todo>
        {
            new Todo { Title = "Buy milk" },
            new Todo { Title = "Leave out the trash" },
            new Todo { Title = "Clean room" }
        }
    });
 
    await context.SaveChangesAsync();
}
 
Console.WriteLine("Querying todos...\n");
 
var results = await QueryTodos("clean milk");
 
Console.WriteLine("\nFound {0} todos\n", results.Count);
 
foreach (var result in results)
{
    Console.WriteLine(result.Title);
}
 
async Task<IList<Todo>> QueryTodos(string searchTerms)
{
    IQueryable<Todo> query = context.Todos;
 
    string[] terms = searchTerms.Split(' ', StringSplitOptions.RemoveEmptyEntries);
 
    foreach (string term in terms)
    {
        query = query.Where(t => t.Title.Contains(term));
    }
 
    Console.WriteLine(query.ToQueryString());
 
    return await query.ToListAsync();
}

In the above code, a new TodoDbContext object is created which will be disposed of at the end of the program.

The EnsureCreated method is then called to ensure that the database is created if it doesn’t already exist. This method will return true if a new database is created and will return false if the database already exists. If a new database is created, the flag that is returned by the EnsureCreated method is used as a simple way to indicate if the database should be seeded with some sample User/Todo data.

Note that this technique is purely used for demo purposes and usually isn’t an approach you should use in production.

Next, the code calls the QueryTodos method that simulates the sort of method we would usually have as part of a Repository. The QueryTodos method accepts a ‘search terms’ string and splits it on the space character to get the individual terms.

The code then iterates through the terms and uses the Where method on the IQueryable object to chain a new predicate to the query, filtering where the title of the todo item contains the term.

The SQL query text is output to the Console for demo purposes using the ToQueryString method.

Lastly, the ToListAsync method is called to materialise the query results which are also output to the Console.

Results

When the program that uses the default chaining behaviour is run, the output will look as follows (red font has been used to emphasise where the current issue lies).

Note that for a real-world application, you will need to consider if a ‘Contains’ search is appropriate since you will be unable to leverage database indexes and performance may suffer when there is a large amount of data to query.

As you can see from the output, the SELECT statement that is generated is querying the ‘Todos’ table where the ‘Title’ column is LIKE the first term AND is also LIKE the second term.

This is not what we want for our particular requirements and is happening because each time we chain a predicate to an IQueryable object, the default behaviour is to treat the filter as an AND condition.

Extending Expressions

To meet our requirements, we can leverage extension methods. Take a look at the ExpressionExtensions class below and add it to your project if you are following along.

using System.Linq.Expressions;
 
/// <summary>
/// Provides <see cref="Expression"/> extension methods.
/// </summary>
public static class ExpressionExtensions
{
    /// <summary>
    /// Combines two predicates into a single predicate using a logical OR operation.
    /// </summary>
    /// <typeparam name="T">The predicate parameter type</typeparam>
    /// <param name="firstPredicate">The first predicate expression to combine</param>
    /// <param name="secondPredicate">The second predicate expression to combine</param>
    /// <returns>A new expression representing the OR combination of the input predicates</returns>
    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> firstPredicate,
        Expression<Func<T, bool>> secondPredicate)
    {
        ArgumentNullException.ThrowIfNull(firstPredicate, nameof(firstPredicate));
        ArgumentNullException.ThrowIfNull(secondPredicate, nameof(secondPredicate));
 
        var invokedExpression = Expression.Invoke(
            secondPredicate,
            firstPredicate.Parameters.Cast<Expression>());
 
        return Expression.Lambda<Func<T, bool>>(
            Expression.OrElse(firstPredicate.Body, invokedExpression),
            firstPredicate.Parameters);
    }
 
    /// <summary>
    /// Combines multiple predicates into a single predicate using a logical OR operation.
    /// </summary>
    /// <typeparam name="T">The predicate parameter type</typeparam>
    /// <param name="predicates">A collection of predicate expressions to combine</param>
    /// <returns>A new expression representing the OR combination of all input predicates</returns>
    public static Expression<Func<T, bool>> Or<T>(
        this IEnumerable<Expression<Func<T, bool>>> predicates)
    {
        ArgumentNullException.ThrowIfNull(predicates, nameof(predicates));
 
        return predicates.Aggregate((aggregatePredicate, nextPredicate) => 
            aggregatePredicate.Or(nextPredicate));
    }
}

The above ExpressionExtensions class defines two extension methods; one that can combine two predicate expressions and another that leverages the first method to combine multiple predicates in one shot.

The first method accepts two generic predicate Func delegates. Expression.Invoke creates a new expression that represents invoking the second predicate using the parameters of the first predicate. It essentially rebinds the parameters of the second predicate to those of the first predicate, allowing them to be merged in the same expression tree context. Expression.Lambda constructs a new lambda expression that combines the body of the first predicate and the invoked expression (the body of the second predicate adapted to the parameters of the first predicate) using the OrElse method. OrElse is used to perform a logical OR operation between the two expressions, meaning that the resulting expression will evaluate to true if either of the original predicates evaluates to true.

The second method accepts a collection of predicate expressions. The Aggregate method is used to combine all of the specified predicates. Aggregate reduces a collection to a single value by applying a function repeatedly. In this case, the function being applied is a lambda that takes two predicates and combines them using the Or extension method that we have already looked at above.

Extended OR behaviour

With the extension methods in place, we can update our program to meet the requirements that were previously laid out. Check out the refactored implementation below.

using Microsoft.EntityFrameworkCore;
using System.Linq.Expressions;
 
using var context = new TodoDbContext();
 
// Code omitted for brevity.
 
async Task<IList<Todo>> QueryTodos(string searchTerms)
{
    IQueryable<Todo> query = context.Todos;
 
    string[] terms = searchTerms.Split(' ', StringSplitOptions.RemoveEmptyEntries);
    var termFilters = new List<Expression<Func<Todo, bool>>>();
 
    foreach (string term in terms)
    {
        termFilters.Add(t => t.Title.Contains(term));
    }
 
    if (termFilters.Any())
    {
        query = query.Where(termFilters.Or());
    }
 
    Console.WriteLine(query.ToQueryString());
 
    return await query.ToListAsync();
}

The updated version of the QueryTodos method above is very similar to the previous version. The primary difference is that we are now building a collection of term filter predicates and then applying these to the query if there are any, using the Or extension method.

When the program is run again with the above code, the output will look as follows (green font has been used to emphasise the key, and the only, difference in the SQL query text).

Note that when additional Where methods are chained to the query e.g. a Status filter, the OR filters in the generated SQL query text will be wrapped in parentheses to ensure that the conditions are evaluated correctly.

As you can see from the above results, the SQL query now looks correct and the expected results have been returned.

Summary

In this article, we have looked at how to dynamically build OR filters in an Entity Framework Core LINQ query by extending the LINQ Expression type with extension methods.

We started by setting up the environment and the types/entities required to test locally.

We first attempted to implement the requirements by appending multiple Where methods to the query and saw that the default chaining behaviour of a LINQ query results in AND conditions.

We then implemented extension methods to give us the desired behaviour and updated our implementation to leverage these to achieve the requirements.