2

I am using Entity Framework and I got a strange issue using PredicateBuilder. I wanted to create a dynamic 'where condition' based on the availability of some values in my request. Even though, predicate variable is assigned, it's not getting added to the underlying sql query. I had checked the query getting executed in SQL and the query getting generated in code. Both doesn't count the predicate for some reason.

Please let me know if I am missing something. Any help is appreciated.

using (DataEntities entities = new DataEntities())
{
   var predicate = PredicateBuilder.True<VpSwiftBank>();
   if (request.BranchItem.Bank != null && !string.IsNullOrEmpty(request.BranchItem.Bank.Name))
       {
        predicate = predicate.Or(i => i.BankName!= null && i.BankName.ToLower().Contains(request.BranchItem.Bank.Name.ToLower()));
       }
      if (request.BranchItem.Country != null && !string.IsNullOrEmpty(request.BranchItem.Country.Name))
        {
        predicate = predicate.Or(i => i.CountryName != null &&  i.CountryName.ToLower().Contains(request.BranchItem.Country.Name.ToLower()));
        }
      if (request.BranchItem.City != null && !string.IsNullOrEmpty(request.BranchItem.City.Name))
        {
                    predicate = predicate.Or(i => i.CityName != null && i.CityName.ToLower().Contains(request.BranchItem.City.Name.ToLower()));
        }

      response.SwiftBankItem = entities.VpSwiftBank.AsExpandable().Where(predicate).ToList().Select(x => new SwiftBankItem
      {
        BankCode = x.BankCode,
        BankName = x.BankName,
        BranchCode = x.BranchCode,
        BranchName = x.BranchName,
        CityCode = x.CityCode,
        CityName = x.CityName,
        CountryCode = x.CountryCode,
        CountryName = x.CountryName,
        SwiftCode = x.SwiftCode
       }).ToArray();
}

Above code just don't generate an SQL where clause for some reason.

Below is the PredicateBuilder extension methods code I have,

  public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}
Sanish Joseph
  • 2,140
  • 3
  • 15
  • 28
  • 1
    Which PredicateBuilder are you using? – Cameron MacFarland Nov 21 '16 at 06:30
  • This is a custom one someone made in my framework. I didn't notice that. Adding the code in question. – Sanish Joseph Nov 21 '16 at 06:33
  • 2
    Since you're building up an expression using `or` conditions, you need to start with `false` as a base, otherwise your additional predicates will have no effect. `true or xxx` is always `true`. – Jeff Mercado Nov 21 '16 at 06:46
  • @JeffMercado Thanks a lot. That seems to be the issue. Can you please add it as an answer so I can accept it? Had to add a null check in the predicate. predicate = predicate.Or(i => i.BankName!= null && i.BankName.ToLower().Contains(request.BranchItem.Bank.Name.ToLower())); – Sanish Joseph Nov 21 '16 at 07:19

0 Answers0