Friday, February 12, 2010

LINQ with PredicateBuilder

When we query using dynamic linq it is tough to execute statements that contain LIKE keyword. LIKE statements in the StringBuilder (which is input to the LINQ quey as a parameter to the WHERE clause) always gave me errors.

But there is an easy solution provided by LINQKit which can be downloaded from http://www.albahari.com/nutshell/linqkit.aspx.

After referring it from the project you can implement a query with ‘Contains’ as described below

   1: var cusPred = PredicateBuilder.True<CustomerEntity>();
   2: if (!String.IsNullOrEmpty(cust.Name))
   3:     cusPred = cusPred.And(s => s.FirstName.Contains(cust.Name)


then the PredicateBuilder can be input to the linq query as given below
   1: IQueryable<Customer> query = (from c in db.CustomerEntities.Where(cusPred)
   2:                         join p in db.PolicyEntities on c.CustomerID equals p.CustomerID
   3:                         join u in db.UserEntities on p.SOCode equals u.SOCode
   4:                         join us in db.UsersInStructureEntities.Where(str.ToString())
   5:                         on u.UserId equals us.UserId                                                      
   6:                         select new Customer
   7:                                {
   8:                                  CustomerId = c.CustomerID,
   9:                                  FirstName = c.FirstName
  10:                                }).Distinct();
  11: