Dynamic LINQ Query In C# Using Predicate Builder

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 implement by albahari. Later this concept 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 expression. We can write a query like Dynamic SQL.

To learn more about predicate delegate 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").Select(i => i), "DropDownID", "Name", ViewBag.Gender);
    ViewBag.PatientType = new SelectList(db.DropDownOptions.Where(i => i.Item == "PatientType").Select(i => i), "DropDownID", "Name", ViewBag.PatientType);
    return View(patients.ToList());
}

In this example I have created an instance of PredicateBuilder with 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>
    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);
    }

    class ParameterRebinder : ExpressionVisitor
    {
        readonly Dictionary<ParameterExpression, ParameterExpression> map;

        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

1. https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/
2. https://msdn.microsoft.com/en-us/library/bb882521%28v=vs.90%29.aspx
3. 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.