C# DynamicLinq where clause with Any()

Question

I want to run dynamic linq with a string where clause like this:

query = db.Customers.Where("Categories.Any(Code == 'Retail')");

Customer entity has collection of categories

class Customer
{
    public List<Category> Categories {get;set;}
    ...
}

class Category
{
    public Guid Id {get;set;}
    public string Code {get;set;}
}

Can anyone please tell me is it possible to do something like this?

PS: I need where clause be string. The where clause will be generated at runtime, for this reason I can't use Linq query expression.

I'm using Telerik DataAccess.

What are the possible variations of the dynamic clause, do you mean the Code is not fixed or the whole condition ? Can you give couple of more different examples ? — Zein Makki, Jun 10 '16 at 05:33
You can use [`Expression`](https://msdn.microsoft.com/en-us/library/ms173144.aspx) for this. Probably you need some builder to build your string query to Expression tree. — Ankush Madankar, Jun 10 '16 at 05:36
There is library for dynamic linq http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library — Eldho, Jun 10 '16 at 06:30

score 6 · Accepted Answer · answered Jun 10 '16 at 06:01

6

It's possible as soon as you follow the Expression Language rules.

For instance, string literals must be enclosed in double quotes:

query = db.Customers.Where("Categories.Any(Code == \"Retail\")");

answered Jun 10 '16 at 06:01

Ivan Stoev

195,425
15
312
343

score 2 · Answer 2 · answered Jun 10 '16 at 05:40

You can build your own runtime Expression:

Expression<Func<Customer, bool>> myRuntimeExpression = null;

if(condition1)
{
    myRuntimeExpression = cust => cust.Categories.Any(cat => cat.Code == "Retial"); // or some local variable
}
else if(condition2)
{
    myRuntimeExpression = cust => cust.Categories.Any(cat => cat.Id = someId) == false;
}
else if(condition3)
{

}

var query = DB.Customers.Where(myRuntimeExpression);

However, if you need to build more complex queries have a look at Dynamic Queries in Linq Using Expressions.

score 1 · Answer 3 · answered Jun 10 '16 at 05:22

1

Shouldn't it be like below?

refinedCustomerList = db.Customers.Where(customer => customer.Categories.Any(Code == 'Retail'));

The above list will contain all the customers whose category is 'Retail'

answered Jun 10 '16 at 05:22

h-rai

3,636
6
52
76

score 1 · Answer 4 · answered Jun 10 '16 at 05:23

1

You need something like this :

query =  db.Customers.Where(x => x.Categories.Where(y => y.Code == "Retail").Any());

answered Jun 10 '16 at 05:23

Anonymous Duck

2,942
1
12
35

I need where clause be string. – Daler Jun 10 '16 at 05:26
@Daler for what reason? – Anonymous Duck Jun 10 '16 at 05:26
Because it's not predefined, clause will be generated on runtime. – Daler Jun 10 '16 at 05:28
Why is it generated at runtime? if there's options why not just have different quires to be run depending on the options – Toxicable Jun 10 '16 at 05:31
1

@Daler i doubt it would be possible as far as i know, you can only pass strings for expressions but for API functions as part of the string, I never encountered cases like that – Anonymous Duck Jun 10 '16 at 05:33
Cause it should work in big project with large number of entities and options. – Daler Jun 10 '16 at 05:34

Avi Turner · Answer 5 · 2016-06-10T06:04:28.207

The linq extention method were accepted an argument of type System.Func<TSource, Int32, Boolean>

In your case a Func that recieves a Customer as an argument and returns true / false.
The result of the Where function would be all the Customers that the Func returned true for.

"Categories.Any(Code == 'Retail')" is a string, not a Func, thus cannot be passed as an argument to the Where method.

Perhaps what you are looking for, if you want to keep the query flexible is some thing like:

Public Customer[] QueryCustomers(Func<Customer,bool> predicate)
{
    var result =  db.Customers.Where(c=> predicate(c)).ToArray();
    return result;
}

Usage:

var retailCustomers =
      QueryCustomers(customer => customer.Categories.Any(Code == 'Retail'))

Or any other query you might compose in run/compile time:

var customersWithNoCategories =
      QueryCustomers(customer => customer.Categories.Count == 0)

C# DynamicLinq where clause with Any()

5 Answers5

Linked