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.

Connect to TFS using C Sharp



Introduction


In this article, we learn how to connect TFS (Team Foundation Server) or VSO (Visual Studio Online) using C#. We can create console application based on TFS API.

Description


Prerequisite

Before starting to working on authenticating TFS using C#, we required TFS Client library reference in the project. You can add required library using a command in NuGet Package Manager.
Install-Package Microsoft.TeamFoundationServer.Client -Version 14.95.3
Install-Package Microsoft.VisualStudio.Services.Client -Version 14.95.3
After adding reference library you can copy below code in your app and add a required reference in the project.

Source Code:


 NetworkCredential credential = new NetworkCredential("username", "password");
 BasicAuthCredential basicCred = new BasicAuthCredential(credential);
 TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(
                    new Uri("https://project.visualstudio.com/DefaultCollection"),
                    basicCred);
 tpc.Authenticate();
      

As per above code, we require valid username and password which is passed to NetworkCredential instance and also require valid TFS URL which you are trying to connect using C#.

We can authenticate a user with basic authentication mechanism without prompting for TFS login dialog, that’s why we have passed credential as an argument in a BasicAuthCredential object. Without basic authentication mechanism app prompt for login information.

TfsTeamProjectCollection instance accept two arguments 1) Default collection Url 2) Credential with basic authentication wrapper. 

TfsTeamProjectCollection instance has authenticate() method which actually requests for authentication and returns result or exception based on the credential.

Conclusion

In this article, we have explored Basic TFS API and authenticated TFS without prompt for username and password.

Login using Google API with C#


Introduction


In this article, we learn about how to login using Google OAuth 2.0 with C#(.NET) or Authenticate Google API in Dot Net.

Description

As we know Google OAuth 2.0 requires ClientId and ClientSecret before we proceeding to further step. So, first of all, we have to generate ClientId and ClientSecret from Google Developer Console by creating credential or generating a credential for Web Application or from Native Application.

I hope you have google account already if not then first create one before proceeding further.

Step 1: Create Credential for OAuth App.
Select credential type OAuth ClientID
Step 2: Select appropriate application type from available option.
Select application type

Based on where we are using login with google account we can select an application type. Here I have use other which works for a console app, Web application hosted on localhost as well as on domain.

Step 3: Copy and save generate ClientId and Client Secret
Save ClientId and Client Secret


Now, you have your ClientId and ClientSecret which will be used for authenticating with Google API using C#.

Once you have setup project you need Google API Reference you can install from Nuget

Prerequiste:

Install-Package Google.Apis -Version 1.32.1

Here is main logic for authenticating google API
public async Task<Usercredential> getUserCredential()
        {
            UserCredential credential;
            string[] scopes = new string[] {  }; // view and manage your Google Analytics data

            //Read client id and client secret from json file

            credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(
                       new ClientSecrets
                       {
                           ClientId = ConfigurationManager.AppSettings["ClientId"],
                           ClientSecret = ConfigurationManager.AppSettings["ClientSecret"]
                       }, scopes,
                "user", CancellationToken.None, new FileDataStore("Auth.Api.Store"));

            return credential;
        }
Hi, here I have created one single method

  • Which grabs ClientId and Client Secret from Web.Config App settings.
  • Passes scopes as a parameter. You can request more detail if you want while requesting from the user
  • Used await and task feature for thread safe and as performance improvement and it is already supported by Google API Library.
When you put above code in your project and run the app it will redirect to Google Account page for validating your credential and request for granting permission to use your basic profile information for login purpose.

If you receive any error it means permission is not given or ClientId or Client Secret key is not valid.

Conclusion

In this article, we have learned about how to create ClientId and Client Secret for a talk with Google API and Login with Google Account functionality using C#.Code.

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 Q...

Learn More