Add the where clause dynamically in Entity Framework

Question

I have this sql statement

SELECT userID from users WHERE (name='name1' AND username='username1') OR (name='name2' AND username='username2') OR (name='name3' AND username='username3') OR .......... (name='nameN' AND username='usernameN') 

How can I implement this statement with entity framework using LINQ?

Answer 1

You can use a beautiful thing called PredicateBuilder. Use it like this

var pr = PredicateBuilder.False<User>(); foreach (var name in names) {     pr = pr.Or(x => x.Name == name && x.Username == name); } return query.AsExpandable().Where(pr); 

Answer 2

 Expression<Func<User, bool>> whereExpression = null;  foreach (var name in names)  {      Expression<Func<User, bool>> e1 = u => u.Name == name;      Expression<Func<User, bool>> andExpression = e1.And(u => u.Username == name);      whereExpression = whereExpression == null ? andExpression : whereExpression.Or(andExpression);  }  return query.Where(whereExpression); 

This helper may help you.

public static class ExpressionExtensions {     public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> leftExpression, Expression<Func<T, bool>> rightExpression)     {         if (leftExpression == null) return rightExpression;         if (rightExpression == null) return leftExpression;         var paramExpr = Expression.Parameter(typeof(T));         var exprBody = Expression.And(leftExpression.Body, rightExpression.Body);         exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);          return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);     }      public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> leftExpression, Expression<Func<T, bool>> rightExpression)     {         if (leftExpression == null) return rightExpression;         if (rightExpression == null) return leftExpression;         var paramExpr = Expression.Parameter(typeof(T));         var exprBody = Expression.Or(leftExpression.Body, rightExpression.Body);         exprBody = (BinaryExpression)new ParameterReplacer(paramExpr).Visit(exprBody);          return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);     } }  class ParameterReplacer : ExpressionVisitor {     private readonly ParameterExpression _parameter;      protected override Expression VisitParameter(ParameterExpression node)     {         return base.VisitParameter(_parameter);     }      internal ParameterReplacer(ParameterExpression parameter)     {         _parameter = parameter;     } }