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.
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!
You are working on a dynamic product search project with the following search request model and simplified product model.
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.
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.
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.
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.
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.