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);
}
}