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?

Amalberga

This blog post by Syed Mehroz Alam contains several examples which are very thorough, and it shows how to combine LINQ query syntax with method syntax in a useful way: LINQ: How to build complex queries utilizing deferred execution and anonymous types – Discrimination

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

Panettone

I've checked this code before . as far as I remember this code doesn't work with entity , only works with linq to sql data models . "I'm not sure about it . have you worked with that ? " – Amalberga

Yes, it supports EF. I use it even with nHibernate, it's brilliant. – Panettone

dear HeavyWave : I've checked again , but got this exception "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities." have you got any Idea about that ? – Amalberga

I tried to use this with EF and I couldn't get it to work. Of course, things might have changed since then. I don't remember what problems I ran in to. – Duo

@Nironline are using EF 4.0? I didn't have any problems with it since it generates a perfectly normal expression tree. Did you remember to use AsExpandable() ? – Panettone

@EgorPavlikhin It works with EF but only in synchronous queries. When I try to call .ToListAsync(), I got exception The source IQueryable doesn't implement IDbAsyncEnumerable<MyEntity>. Only sources that implement IDbAsyncEnumerable can be used – Mascon

Game changer! Works great and simple to use – Osteomalacia

Has the library been updated to support async wait? – Undertaking

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

and:

class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _parameter;

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return base.VisitParameter(_parameter);
    }

    internal ParameterReplacer(ParameterExpression parameter)
    {
        _parameter = parameter;
    }
}

Dynamiter

Answer 3

NOTE: this is modified from something I have so it might not work out of the box. But it would be a good starting point.

public static IQueryable<TEntity> Where<TEntity>(
    this IQueryable<TEntity> source,
    IEnumerable<WhereSpecifier> orClauses) where TEntity : class
{
    if (!orClauses.Any()) return source.Where(t => false);
    Type type = typeof (TEntity);
    ParameterExpression parameter = null;
    Expression predicate = Expression.Constant(false, typeof (bool));
    ParameterExpression whereEnt = Expression.Parameter(type, "WhereEnt");
    foreach (WhereSpecifier orClause in orClauses)
    {
        Expression selector;
        if (orClause.Selector != null) {
            selector = orClause.Selector;
            parameter = orClause.Parameter;
        }
        else
        {
            parameter = whereEnt;
            Type selectorResultType;
            selector = GenerateSelector<TEntity>(parameter, orClause.Column, 
                out selectorResultType);
        }
        Expression clause = selector.CallMethod(orClause.Method, 
            MakeConstant(selector.Type, orClause.Value), orClause.Modifiers);
        predicate = Expression.Or(predicate, clause);
    }

    var lambda = Expression.Lambda(predicate, whereEnt);
    var resultExp = Expression.Call(typeof (Queryable), "Where", new[] {type},
        source.Expression, Expression.Quote(lambda));
    return source.Provider.CreateQuery<TEntity>(resultExp);
}

GenerateSelector:

public static Expression GenerateSelector<TEntity>(
    ParameterExpression parameter, string propertyName,
    out Type resultType) where TEntity : class
{
    //  create the selector part, but support child properties
    PropertyInfo property;
    Expression propertyAccess;
    if (propertyName.Contains('.'))
    {
        // support to be sorted on child fields.
        String[] childProperties = propertyName.Split('.');
        property = typeof (TEntity).GetProperty(childProperties[0]);
        propertyAccess = Expression.MakeMemberAccess(parameter, property);
        for (int i = 1; i < childProperties.Length; i++)
        {
            property = property.PropertyType.GetProperty(childProperties[i]);
            propertyAccess = Expression
                .MakeMemberAccess(propertyAccess, property);
        }
    }
    else
    {
        property = typeof (TEntity).GetProperty(propertyName);
        propertyAccess = Expression.MakeMemberAccess(parameter, property);
    }
    resultType = property.PropertyType;
    return propertyAccess;
}

WHereSpecifier:

public class WhereSpecifier
{
    public WhereSpecifier(string column, CheckMethod method, string value, 
        CheckMethodModifiers modifiers)
    {
        Modifiers = modifiers;
        Value = value;
        Column = column;
        Method = method;
    }

    public WhereSpecifier(string column, CheckMethod method, string value)
        : this(column, method, value, CheckMethodModifiers.None)
    {
    }
    public Expression Selector { get; set; }
    public ParameterExpression Parameter { get; set; }
    public string Column { get; set; }
    public CheckMethod Method { get; set; }
    public CheckMethodModifiers Modifiers { get; set; }
    public string Value { get; set; }
}

Usage:

var column = typeof(TEntity).Name + "ID";
var where = from id in SelectedIds 
            select new WhereSpecifier(column, CheckMethod.Equal, id.ToString());
return GetTable().Where(where);

Duo

Answer 4

I tried @Egor Pavlikhin solution but i got "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.".

According to this you can use PredicateExtensions :

var predicate = PredicateExtensions.Begin<User>();
foreach (var name in names)
{
    pr = pr.Or(x => x.Name == name);
}
return _context.Users.Where(predicate);

Ulrikaumeko

Answer 5

Don't forget that entity framework also understands entity sql, so you can do this part of the query in a string. Building a string up is pretty convenient when you have dynamic stuff you need to do.

Biogen

Answer 6

I had to construct the predicate for the 'Where' clause dynamically based on User Interface selections. 'System.Dynamic.Linq' allows to predicates from strings.

foreach (var name in names)
{
    query = query.Where("Name=@0 And UserName=@1", name, name);
}
return query;

'System.Dynamic.Linq' is available as a nuget package. Check out Scott Guthrie's introduction to the topic here.

Xylem

this only allows ANDs but not ORs – Kirin

Answer 7

i found this way it is too simple :

    var query = context.InvoiceHeader.Where( i =>  i.DateInvoice >= model.datedu && i.DateInvoice <= model.dateau).AsQueryable();
        if(model.name != null)
        {
            query = query.Where(i =>  i.InvoiceNum.Equals(model.name));
        }
        if (model.status != 0 )
        {
            query = query.Where(i => i.REF_InvoiceStatusRecId == model.status);
        }

        if (model.paiements != 0)
        {
            query = query.Where(i => i.REF_PaymentTermRecId  == model.paiements);
        }
        query = query.AsQueryable().OrderByDescending(x => x.RecId);

Monazite

Answer 8

For System.Dynamic.Linq

This is dynamic sql:

//using System.Linq.Dynamic.Core;
//using Microsoft.EntityFrameworkCore.Metadata;

var listName = Enumerable.Range(1, 6).Select(i=>new
{
    name=$"name{i}",
    username=$"username{i}"
}).ToList();

var listOr = new List<string>();
var entityType = _context.XXX.EntityType;
var schema = entityType.GetSchema();
var tableName = entityType.GetTableName();
var storeObjectIdentifier = StoreObjectIdentifier.Table(tableName, schema);
var name = entityType.FindProperty(nameof(XXX.Name)).GetColumnName(storeObjectIdentifier);
var userName = entityType.FindProperty(nameof(XXX.UserName)).GetColumnName(storeObjectIdentifier);
foreach (var tParm in listName)
{
    listOr.Add($"({name}={tParm.name} AND {userName}={tParm.username})");
}
var result = _context.XXX.Where(string.Join(" OR ", listOr));

var sql = query.ToQueryString();

Drag