Introduction to predicate builder
Have you ever come across a situation that you need to build a dynamic query to fetch data from database?
Building queries dynamically can be really painful and time consuming. But nothing to worry, we have some libraries to save us from this hassle (thanks to the developers who built those libraries). One of the predicate builder packages we can use is LinQKit which is very powerful and easy to use. So lets jump into the predicate builder implementation and see how this works and how can we use this in our project.
Case study
First, let’s see some situation where we need to use this kind of dynamic query. Lets assume that we have a table with thousands of customers’ vehicles along with an internal unique number called VIN.
So the table data will be like below. There is no customer Id here and just a vehicle list.
| Id | Make | Model | Year | VIN |
| 1 | Toyota | Camry | 2000 | XTS12345 |
| 2 | Toyota | Corolla | 2000 | XTG12346 |
So whenever customer comes to our website and do a particular request, they enter their vehicles Make, Model and Year in UI and we need to get those vehicles VINs by those respective Make, Model and Year. A particular customer can have multiple vehicles. So the vehicles list is a dynamic list depending on customer. Retrieve all the vehicles from the database and filter in memory is not an efficient way since there can be thousands of records in database. In this case we need to fetch only the matching vehicles. There can be several ways to build the query dynamically, but here we are walking with a predicate builder to do this.
Create a new project
First, we need to create a console application. This will be a basic application with a static list of vehicles pretend as database. Then install LinqKit nuget package using package manager.
Then add a class called DataRepository.cs. This class will act as database call handler and we add the static vehicles list to mimic the database.
Create the static list to pretend database data
First, I’m creating the Vehicle class
internal class Vehicle
{
public int Id { get; set; }
public string Make { get; set; }
public string Model { get; set; }
public string VIN { get; set; }
public int Year { get; set; }
}
Then create a vehicles list in DataRepository class. I used ChatGPT to generate this sample vehicles list 😉
internal class DataRepository
{
public readonly List<Vehicle> VehiclesList = new List<Vehicle>
{
new Vehicle { Id = 1, Make = "Toyota", Model = "Camry", VIN = "XTS1234", Year = 2020},
new Vehicle { Id = 2, Make = "Toyota", Model = "Corolla", VIN = "VTS1234", Year = 2020},
new Vehicle { Id = 3, Make = "Honda", Model = "Civic", VIN = "ATS5678", Year = 2021},
new Vehicle { Id = 4, Make = "Honda", Model = "Accord", VIN = "BTS5678", Year = 2021},
new Vehicle { Id = 5, Make = "Ford", Model = "F-150", VIN = "CTS9012", Year = 2022},
new Vehicle { Id = 6, Make = "Chevrolet", Model = "Silverado", VIN = "DTS9012", Year = 2022},
new Vehicle { Id = 7, Make = "Nissan", Model = "Altima", VIN = "ETS3456", Year = 2020},
new Vehicle { Id = 8, Make = "Nissan", Model = "Sentra", VIN = "FTS3456", Year = 2020},
new Vehicle { Id = 9, Make = "Tesla", Model = "Model S", VIN = "GTS6789", Year = 2023},
new Vehicle { Id = 10, Make = "Tesla", Model = "Model 3", VIN = "HTS6789", Year = 2023},
new Vehicle { Id = 11, Make = "Audi", Model = "A4", VIN = "ITS2345", Year = 2020},
new Vehicle { Id = 12, Make = "Audi", Model = "A6", VIN = "JTS2345", Year = 2020},
new Vehicle { Id = 13, Make = "BMW", Model = "3 Series", VIN = "KTS4567", Year = 2021},
new Vehicle { Id = 14, Make = "BMW", Model = "5 Series", VIN = "LTS4567", Year = 2021},
new Vehicle { Id = 15, Make = "Mercedes-Benz", Model = "C-Class", VIN = "MTS7890", Year = 2022},
new Vehicle { Id = 16, Make = "Mercedes-Benz", Model = "E-Class", VIN = "NTS7890", Year = 2022},
new Vehicle { Id = 17, Make = "Lexus", Model = "ES", VIN = "OTS9012", Year = 2020},
new Vehicle { Id = 18, Make = "Lexus", Model = "IS", VIN = "PTS9012", Year = 2020},
new Vehicle { Id = 19, Make = "Mazda", Model = "Mazda3", VIN = "QTS5678", Year = 2021},
new Vehicle { Id = 20, Make = "Mazda", Model = "Mazda6", VIN = "RTS5678", Year = 2021},
new Vehicle { Id = 21, Make = "Subaru", Model = "Impreza", VIN = "STS2345", Year = 2022},
};
}
Build the predicate
Then, we need to implement the method to get only the vehicles which are matching with customer’s Make, Model and Year. In Program.cs, I’m trying to retrieve two lists of vehicles, assuming for two customers’ vehicles and print them. One has 3 vehicles and the other one has 5 vehicles.
internal class Program
{
static void Main(string[] args)
{
DataRepository repository = new DataRepository();
List<Vehicle> vehclesToBeReturned = new List<Vehicle>
{
new Vehicle { Make = "Honda", Model = "Accord", Year = 2021},
new Vehicle { Make = "Ford", Model = "F-150", Year = 2022},
new Vehicle { Make = "Chevrolet", Model = "Silverado",Year = 2022},
};
// First we retrieve for 3 vehicles
var vehicles = repository.GetVehicles(vehclesToBeReturned);
foreach (var vehicle in vehicles)
{
Console.WriteLine($" VIN is {vehicle.VIN} for {vehicle.Make}, {vehicle.Model},{vehicle.Year}");
}
vehclesToBeReturned = new List<Vehicle>
{
new Vehicle { Make = "Lexus", Model = "ES", Year = 2020},
new Vehicle { Make = "Lexus", Model = "IS", Year = 2020},
new Vehicle { Make = "Mazda", Model = "Mazda3", Year = 2021},
new Vehicle { Make = "Mazda", Model = "Mazda6", Year = 2021},
new Vehicle { Make = "Subaru", Model = "Impreza", Year = 2022},
};
// Now we retrieve for 4 vehicles
vehicles = repository.GetVehicles(vehclesToBeReturned);
foreach (var vehicle in vehicles)
{
Console.WriteLine($" VIN is {vehicle.VIN} for {vehicle.Make}, {vehicle.Model},{vehicle.Year}");
}
}
}
Now we need to support this dynamic vehicles list from our repository. So I’m adding below method to support this.
public IEnumerable<Vehicle> GetVehicles(List<Vehicle> vehicles)
{
// Initialize new predicate
var predicate = PredicateBuilder.New<Vehicle>();
foreach (var vehicle in vehicles)
{
//Adding each vehicles into the predicate
predicate = predicate.Or(v => v.Make == vehicle.Make && v.Model == vehicle.Model && v.Year == vehicle.Year);
}
return VehiclesList.Where(predicate).ToList();
}
Here, we are going through each customer’s vehicles and adding them to the predicate query. So the expression will be passed to the Where clause. This can be passed to the Entity Framework database context in similar way.
Summary
In summary, we just had to add a few clean lines of codes to filter a large list based on a small dynamic list. Using a predicate builder, we can easily build dynamic queries efficiently.