Generate dynamic LINQ expression based on array

Question

I am using LINQ exprssion to query customers and filter them by state names. I have following query which works fine till the time I have 4 items in my statesArray.

public void GetCustomersForSelectedStates(string[] statesArray)
{
    var customers = _repo.GetAllCustomers();
    var filteredCustomers = from CUST in customers
    join ST in States on CT.Tag_Id equals ST.Id                       
    where CUST.ID == customer.ID  && (ST.Name == statesArray[0] ||ST.Name ==statesArray[1] || ST.Name== statesArray[2]||ST.Name =statesArray[3])

    //Do something with customers

}

I want following exprssion to be created dynamically:

(ST.Name == statesArray[0] ||ST.Name ==statesArray[1] ||    
 ST.Name== statesArray[2]||ST.Name =statesArray[3])

For example , create the dynamicQuery like below

var dynamicQuery = "(";
var dynamicQuery = "(";
for (int i = 0; i < statesArray.Count(); i++)
    {
        dynamicQuery += "ST.Name =="+statesArray[0];
        if(i==statesArray.Count())
        dynamicQuery+=")"
    }

and then use it something like following,

//Psuedo code
var customers = _repo.GetAllCustomers();
    var filteredCustomers = from CUST in customers
    join ST in States on CT.Tag_Id equals ST.Id                       
    where CUST.ID == customer.ID  && Expression(dynamicQuery)

@JohnWu, Thanks , this works , was making this unnecessarily complicated. But will keep the question open (to know how to generate expression dynamically) — Simsons
– Simsons, Commented Apr 5, 2018 at 5:48

Marc Gravell · Accepted Answer · 2018-04-05 10:46:17Z

To do that via dynamic expressions basically means building a tree of:

(x.Foo == val0 || x.Foo == val1 || x.Foo == val2)

You can do that like this:

static Expression<Func<T, bool>> Where<T, TVal>(Expression<Func<T, TVal>> selector,
    IEnumerable<TVal> values)
{
    Expression result = null;
    foreach (var val in values)
    {
        var match = Expression.Equal(
            selector.Body,
            Expression.Constant(val, typeof(TVal)));

        result = result == null ? match : Expression.OrElse(result, match);
    }
    if (result == null) return x => true; // always match if no inputs

    return Expression.Lambda<Func<T, bool>>(result, selector.Parameters);
}

with example usage:

string[] names = { "a", "c" };
var predicate = Where<Customer, string>(c => c.Name, names);

You can then use this predicate in the IQueryable<T>.Where extension method.

To combine it in your case, first do your regular LINQ:

var customers = _repo.GetAllCustomers();
var filteredCustomers = from CUST in customers
join ST in States on CT.Tag_Id equals ST.Id                       
where CUST.ID == customer.ID;

Now as a separate step apply the extra filter:

customers = customers.Where(predicate);

What this does is accept an input lambda of the form c => c.Name, then reuses the c.Name body for each c.Name == {val}, and reuses the c parameter as the parameter for the lambda we're creating. Each value from values becomes a typed constant. The Expression.Equal gives us the == test. The OrElse is the ||, noting that if result is null, this is the first item, so we just use the match expression itself.

Oxoron · Accepted Answer · 2018-04-05 10:27:55Z

1

You can add another join

var customers = _repo.GetAllCustomers();

var filteredCustomers = from CUST in customers
    join ST in States on CT.Tag_Id equals ST.Id  
    join SA in statesArray on ST.Name equals SA // No need dynamic expression now          
where CUST.ID == customer.ID 

//Do something with customers

answered Apr 5, 2018 at 10:27

Oxoron

6741 gold badge8 silver badges27 bronze badges

4 Comments

Marc Gravell Over a year ago

I would expect this to either fail (bad), or work by performing the join in-memory locally (much worse)

Oxoron Over a year ago

@MarcGravell based on _repo.GetAllCustomers() - we are locally already. But yes, if we join to DB through EF with such join - we'll fail.

Marc Gravell Over a year ago

that doesn't follow; I'm guessing that the repo has something like; public IQueryable<Customer> GetAllCustomers() => db.Customers; - it doesn't suggest we're fetching anything. Also, the question is tagged linq-to-sql, strongly suggesting this approach

Oxoron Over a year ago

@MarcGravell that's a situation where we have "it depends". If we are locally already - join will be short and simple. In some situations (with small amount of data in repo) this works fine. If we have EF (standard for .NET) - we'll fail. If we have other IQueryable - we'll be depend on provider. Your answer is much more universal and elegant, plus it correlates with a question better.

Collectives™ on Stack Overflow

Generate dynamic LINQ expression based on array

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related