Saturday, 17 February 2018

Dynamic Query in LINQ using Predicate Builder

Introduction

This tutorial explain how to create dynamic query using LINQ, Using Predicate Builder  LINQ to SQL dynamic query and Query with Entity Framework is easy. This concept first implement by albahari

Description

Predicate Builder is power full LINQ expression which is mainly used when too many search filter parameter is there for querying data by writing dynamic query expression. We can write query like Dynamic SQL.

To know more about predicate delegate visit Predicate Delegate

How To Implement Predicate Builder

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 instance of PredicateBuilder with PatientInfo Model and add multiple OR and AND Condition based on their value. Predicate Builder automatically create dynamic query with Linq and combined into one Expression.

When We have grid which filters record based on applied filter and filter parameter is in large number decision of use Dynamic LINQ result very high performance and minimize code writing while implementation otherwise it requires number of if else statement based on filter parameter.

In above code I have used entity framework Entity function which is very useful when we have to perform DateTime operation. It is internally worked as 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);
            }
        }
    }
 


Above Predicate builder helper method reference from albahari and from stackoverflow article and learn how powerful feature it is.predicate builder also work with IEnumerable and IQueryable.

Conclusion:

This article explain implementation of Dynamic Linq query using Predicate Builder. Hope this article is useful while implementing dynamic query using LINQ.
Thanks to Pavel Valdov for C# code syntax highlighter.

1 comment:

Saturday, 17 February 2018

Dynamic Query in LINQ using Predicate Builder

Introduction

This tutorial explain how to create dynamic query using LINQ, Using Predicate Builder  LINQ to SQL dynamic query and Query with Entity Framework is easy. This concept first implement by albahari

Description

Predicate Builder is power full LINQ expression which is mainly used when too many search filter parameter is there for querying data by writing dynamic query expression. We can write query like Dynamic SQL.

To know more about predicate delegate visit Predicate Delegate

How To Implement Predicate Builder

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 instance of PredicateBuilder with PatientInfo Model and add multiple OR and AND Condition based on their value. Predicate Builder automatically create dynamic query with Linq and combined into one Expression.

When We have grid which filters record based on applied filter and filter parameter is in large number decision of use Dynamic LINQ result very high performance and minimize code writing while implementation otherwise it requires number of if else statement based on filter parameter.

In above code I have used entity framework Entity function which is very useful when we have to perform DateTime operation. It is internally worked as 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);
            }
        }
    }
 


Above Predicate builder helper method reference from albahari and from stackoverflow article and learn how powerful feature it is.predicate builder also work with IEnumerable and IQueryable.

Conclusion:

This article explain implementation of Dynamic Linq query using Predicate Builder. Hope this article is useful while implementing dynamic query using LINQ.
Thanks to Pavel Valdov for C# code syntax highlighter.

1 comment: