Using PredicateBuilder with EF Core for Complex Queries

In a perfect world, all of your EF Queries will be simplistic and not require complex logic, and the EF system itself will generate high-quality & performant queries in the process. Sadly, this utopia doesn't exist; however, tools are available, including PredicateBuilder that makes things work well with minimal effort.

Understanding The Problem

Queries that are consistent, with the same criteria at all times can typically be constructed in a manner that will support complex queries without hesitation. You can add a single where clause to EF that includes a combination of AND (&&) or OR (||) statements and everything will work as desired.

_context.Products.Where(p.Cost > 150 || (p.Cost > 75 && p.IsFeatured);

However, creating a query with dynamic criteria containing combinations of AND and OR can be complicated. PredicateBuilder is here to help and can make life easy!

The Scenario

You are working on a dynamic product search project with the following search request model and simplified product model.

public class SearchFilter
{
    public List Keywords { get; set; }
    public List CategoryIds { get; set; }
    public bool ActiveOnly { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public string Description { get; set; }
    public int CategoryId { get; set; }
    public bool IsActive { get; set; }
}

Your goal is to create a query to list all Products and conditionally include search criteria with the following limitations.

• If Keywords are not supplied, no impact should occur to the query.
• If any Keywords are supplied, your search result must return results if any ONE keyword is found in the product name, or description.
• If CategoryIds are not supplied, no impact should occur to the query.
• If any CategoryIds are supplied, your search result must return a match if the CategoryId of the Product is found in the list.
• If the ActiveOnly flag is set to true, you must only show active.

Working the Solution

You can start by implementing the final three requirements as they are much easier to implement. This can be accomplished with the following query.

 public List SearchProducts(SearchFilter mySearchFilter)
{
    var result = _context.Products.AsNoTracking();

    if(mySearchFilter.IsActive)
        result = result.Where(p => p.ActiveOnly);

    if(mySearchFilter.CategoryIds != null && mySearchFilter.CategoryIds.Any())
        result = result.Where(p => mySearchFilter.CategoryIds.Contains(p.CategoryId));

    return result.ToList();
} 

Nothing is too earth-shattering in this query. If a filter criterion is required, the query is updated; each of these additions execute as AND conditions. In other words, if provided an IsActive limitation and a CategoryId list, the product must meet BOTH criteria for a result to be returned. As you look to implement a keyword search this will no longer work.

Introducting LinqKit & PredicateBuilder

Thankfully there is help! LinqKit is a free library that provides a number of additional features. You will utilize the Predicate Builder functionality to create a new filter critera, and attach it to your existing query.

Including LinqKit

LinqKit is installed via a NuGet package by the name of LinqKit.Microsoft.EntityFrameworkCore with support for .NET Core 1.1, 2.0 and 3.0. This library provides a large number of functions and their documentation provides a number of helpful examples.

For your project with the keyword search requirements a PredicateBuilder object will be created, if keywords were included, and fo each keyword OR conditions will be added and the resultant added to the query. The following figure shows an example of this.

public List SearchProducts(SearchFilter mySearchFilter)
{
    var result = _context.Products
                    .AsNoTracking()
                    .AsExpandable();

    if(mySearchFilter.IsActive)
        result = result.Where(p => p.ActiveOnly);

    if(mySearchFilter.CategoryIds != null && mySearchFilter.CategoryIds.Any())
        result = result.Where(p => mySearchFilter.CategoryIds.Contains(p.CategoryId));

    if(mySearchFilter.Keywords != null && mySearchFilter.Keywords.Any())
    {
        //Crate the builder
        var predicate = PredicateBuilder.New();

        //Loop through the keywords
        foreach(var item in mySearchFilter.Keywords)
        {
            var currentKeyword = item;
            predicate = predicate.Or (p => p.ProductName.Contains (currrentKeyword));
            predicate = predicate.Or (p => p.Description.Contains (currrentKeyword));
        }
        result = result.Where(predicate);
    }

    return result.ToList();
} 

This code sample contained two key provisions. You indicate to EntityFramework that we want to use the new Expandable features introduced by LinqKit using the AsExpandable() call. Then a predicate is built out using a dynamically created OR clause, and the entire predicate is added to the query with all other criteria.

In Summary

Complex queries in Entity Framework can often get a bad rap. Having tools like LinqKit and PredicateBuilder in your toolbox can take EF nightmares and turn them into success stories.