Add the where clause dynamically in Entity Framework

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?

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

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