Introduction
This tutorial explains how to create a dynamic LINQ query in C#. Using the Predicate Builder we can create LINQ to SQL dynamic query and Query with Entity Framework is easy. This concept was first implemented by Albahari. Later this concept was extended by Monty’s Gush as Universal PredicateBuilder.
Description
Predicate Builder is a powerful LINQ expression that is mainly used when too many search filter parameters are used for querying data by writing dynamic query expressions. We can write a query like Dynamic SQL.
To learn more about predicate delegates visit Predicate Delegate.
How to use predicate builder to create dynamic LINQ query
Model
public class PatientInfo
{
public int PatientID { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
[DataType(DataType.DateTime)]
public Nullable<System.DateTime> BirthDate { get; set; }
public string Gender { get; set; }
public string PatientType { get; set; }
public string InsuranceNumber { get; set; }
[Required]
[DataType(DataType.DateTime)]
public Nullable<System.DateTime> AdmissionDate { get; set; }
public bool IsHaveInsurence { get; set; }
}
Namespace
using System;
using System.Data;
using System.Data.Objects;
using System.Data.Entity;
using System.Linq;
using System.Web.Mvc;
Implementation
public ActionResult Index(string PatientName, string BirthDate, string Gender, string PatientType)
{
ViewBag.PatientName = PatientName ?? "";
ViewBag.BirthDate = BirthDate ?? "";
ViewBag.Gender = Gender ?? "";
ViewBag.PatientType = PatientType ?? "";
var predicate = PredicateBuilder.True<Patient>();
if (!string.IsNullOrEmpty(PatientName))
{
predicate = predicate.And(i => i.FirstName.ToLower().StartsWith(PatientName) || i.LastName.ToLower().StartsWith(PatientName));
}
if (!string.IsNullOrEmpty(Gender))
{
int gender;
Int32.TryParse(Gender, out gender);
predicate = predicate.And(i => i.Gender == gender);
}
if (!string.IsNullOrEmpty(PatientType))
{
int type;
Int32.TryParse(PatientType, out type);
predicate = predicate.And(i => i.PatientType == type);
}
if (!string.IsNullOrEmpty(BirthDate))
{
DateTime dob;
DateTime.TryParse(BirthDate, out dob);
predicate = predicate.And(i => EntityFunctions.TruncateTime(i.BirthDate) == EntityFunctions.TruncateTime(dob));
}
var patients = db.Patients
.Where(predicate)
.Select(i => i)
.Include(p => p.DropDownOption)
.Include(p => p.DropDownOption1);
ViewBag.Gender = new SelectList(db.DropDownOptions.Where(i => i.Item == "Gender"), "DropDownID", "Name", ViewBag.Gender);
ViewBag.PatientType = new SelectList(db.DropDownOptions.Where(i => i.Item == "PatientType"), "DropDownID", "Name", ViewBag.PatientType);
return View(patients.ToList());
}
In this example, I have created an instance of PredicateBuilder with the PatientInfo Model and added multiple OR and AND conditions based on their value. Predicate Builder automatically creates a dynamic query with LINQ and combines it into one expression.
When we have a grid that filters records based on an applied filter and there are many filter parameters, the decision to use Dynamic LINQ results in much better performance and it minimizes the amount of code to be written for the implementation, otherwise, it requires many if/else statements based on the filter parameter.
In the preceding code, I have used an Entity Framework Entity function that is very useful when we need to perform DateTime operations. It internally works as a SQL DateTime function.
PrecateBuilder.cs
/// <summary>
/// Enables the efficient, dynamic composition of query predicates.
/// </summary>
public static class PredicateBuilder
{
/// <summary>
/// Creates a predicate that evaluates to true.
/// </summary>
public static Expression<Func<T, bool>> True<T>() { return param => true; }
/// <summary>
/// Creates a predicate that evaluates to false.
/// </summary>
public static Expression<Func<T, bool>> False<T>() { return param => false; }
/// <summary>
/// Creates a predicate expression from the specified lambda expression.
/// </summary>
public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }
/// <summary>
/// Combines the first predicate with the second using the logical "and".
/// </summary>
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
{
return first.Compose(second, Expression.AndAlso);
}
/// <summary>
/// Combines the first predicate with the second using the logical "or".
/// </summary>
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
{
return first.Compose(second, Expression.OrElse);
}
/// <summary>
/// Negates the predicate.
/// </summary>
public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
{
var negated = Expression.Not(expression.Body);
return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
}
/// <summary>
/// Combines the first expression with the second using the specified merge function.
/// </summary>
private static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
{
// zip parameters (map from parameters of second to parameters of first)
var map = first.Parameters
.Select((f, i) => new { f, s = second.Parameters[i] })
.ToDictionary(p => p.s, p => p.f);
// replace parameters in the second lambda expression with the parameters in the first
var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);
// create a merged lambda expression with parameters from the first expression
return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
}
private class ParameterRebinder : ExpressionVisitor
{
readonly Dictionary<ParameterExpression, ParameterExpression> map;
private ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
{
this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
}
public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
{
return new ParameterRebinder(map).Visit(exp);
}
protected override Expression VisitParameter(ParameterExpression p)
{
ParameterExpression replacement;
if (map.TryGetValue(p, out replacement))
{
p = replacement;
}
return base.VisitParameter(p);
}
}
}
The preceding Predicate Builder helper method is referenced from Albahari and this Stackoverflow article. Predicate builder also works with IEnumerable and IQueryable.
Reference Links
- https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/
- https://msdn.microsoft.com/en-us/library/bb882521%28v=vs.90%29.aspx
- https://blogs.msdn.microsoft.com/meek/2008/05/02/linq-to-entities-combining-predicates/
Conclusion
This article explains the implementation of a Dynamic LINQ query using Predicate Builder. I hope this article is useful when implementing dynamic queries using LINQ.