Is it possible to use a string for a LINQ query expression?

Question

I need to extract some records if some variables have some values.

For example, if status>0 I need to filter result like :

where object.id=status

else, if status=0, I need to remove this where clauses and return all elements. I'll get rid about :

if(status>0)
   do a linq query with the where clauses
else
   do a link query with that where clauses

too much code, because the variables to check could be more than 4-5.

Is it possible to "inject" a sort of string on LINQ? (So i can create my string and pass it to the LINQ).

I mean somethings like :

string myQuery="";
if(status>0)
   myQuery="where object.id=status";
else
   myQuery="";

is it possible? (Classic mysql behaviour).

This has been asked many times. Please search "dynamic LINQ". — Dan Abramov, Jul 27 '11 at 15:40
It is possible using dynamic linq - see http://stackoverflow.com/questions/5139467/how-to-create-linq-query-from-string/5139491#5139491 — Stuart, Jul 27 '11 at 15:41

score 9 · Accepted Answer · answered Jul 27 '11 at 15:41

9

Since LINQ is lazy, you can just do

var query = ...

if (status > 0)
{
    query = query.Where(o => o.id == status);
}

answered Jul 27 '11 at 15:41

hammar

138,522
17
304
385

Looks interessant this approch! But in fact I do 2 queries : one for extract ALL records, one filtering with this approch. Is not a waste of resources? – markzzz Jul 27 '11 at 15:50
If you don't have many records, you can perform only one query for all records, then filter the collection in memory using LINQ to Objects – Vasea Jul 27 '11 at 15:55
1

@markzzz: Only one query is run here. You can keep building the query adding `Where()` clauses; the query isn't executed until you demand the results. If you want to first get all the records and then filter locally, you can call `ToList()` to force the query to be executed. – hammar Jul 27 '11 at 15:56
Uhm...in fact I don't do the ToList() at all. I think it will be executed when I run it into a foreach? Hope do you see what I saying.. :) – markzzz Jul 27 '11 at 16:01
1

Yep, `foreach` will force execution of the query as well. – hammar Jul 27 '11 at 16:08

score 3 · Answer 2 · answered Jul 27 '11 at 15:43

3

You can build up a query like this:

IEnumerable<MyEntity> results = MyEntityContext.MyEntities;
if (status > 0)
    results = results.Where(e => e.id == status);

Does that help?

answered Jul 27 '11 at 15:43

Chris Snowden

4,982
1
25
34

As I said before, this means to execute 2 queries : one for extract all records, one for filter with the where clause. Not so good :( – markzzz Jul 27 '11 at 15:51
1

Actually, because it's an `IEnumerable` (`IQueryable` works the same too), there is no database hit in building up the query until you call `ToArray()` or `ToList()` to use the data which will trigger a single database hit. – Chris Snowden Jul 27 '11 at 15:56
1

@markzzz: No! This does NOT execute two queries. In fact, NO queries are executed here. The query is not executed until you iterate the results by calling ToList (for example) or use a foreach. – Chris Dunaway Jul 27 '11 at 21:09

score 3 · Answer 3 · answered Jul 27 '11 at 15:43

3

It is possible using Dynamic LINQ, see ScottGu's blog post: Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library)

enter image description here

answered Jul 27 '11 at 15:43

sll

61,540
22
104
156

score 2 · Answer 4 · answered Jul 27 '11 at 15:43

2

You could do it like this:

  var query = database.MyTable.Where(/* where for all records */);

  if (status > 0) {
      query = query.Where(o => o.id == status);
  }

Linq (to sql and EF) is smart enough to merge the where conditions and send one SQL statement to the database.

answered Jul 27 '11 at 15:43

GvS

52,015
16
101
139

Where clause not needed at all here. Just use `database.MyTable;` – Chris Snowden Jul 27 '11 at 15:46
As I said before, this means to execute 2 queries : one for extract all records, one for filter with the where clause. Not so good :( – markzzz Jul 27 '11 at 15:52
2

@markzzz That's not true. What and when things are executed is dependent to a degree on your particular LINQ provider, but for a competent SQL implementation (EF, Linq2Sql, NHibernate), nothing is executed at all in the above code, and when you do execute it in a later iteration only one query will be made, regardless of which branch was taken in generating the query expression itself. – Matt Enright Jul 27 '11 at 18:38
If you have Visual studio ultimate, you can easily see what and when is executed, with Intellitrace. You will see that only one query is executed with the above code. – GvS Jul 27 '11 at 20:57

score 1 · Answer 5 · edited May 23 '17 at 10:34

1

It is possible using dynamic linq - see How to create LINQ Query from string?

My answer there links to Scott Gu's posts and the sample code from Microsoft - http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

edited May 23 '17 at 10:34

Community

1
1

answered Jul 27 '11 at 15:42

Stuart

66,722
7
114
165

Vasea · Answer 6 · 2011-07-27T15:54:26.647

1

You can write the following

IQueryable<T> query = dbContext.SomeObjectSet;

if (condition1) {
    query = query.Where(...)
}

if (condition2) {
    query = query.Where(...)
}

However, you you want to query all entities, you can filter in memory afterwards using LINQ to SQL

edited Jul 27 '11 at 15:54

answered Jul 27 '11 at 15:43

Vasea

5,043
2
26
30

score 1 · Answer 7 · answered Jul 27 '11 at 15:44

1

AFAIK, you will need to have 2 different queries.

if(status > 0)
{
    var myquery = From ....
    where object.id = status
}
else
{
    var myquery = From ..
}

answered Jul 27 '11 at 15:44

Vinay

1,016
8
22

score 1 · Answer 8 · answered Jul 27 '11 at 15:46

1

Another option: query.Where(x=>(status>0? x.id==status : 1==1))

answered Jul 27 '11 at 15:46

a1ex07

36,826
12
90
103

This is slightly less optimal than just adding the Where clause in a separate branch, because even in the 1==1 case you still have the overhead of a delegate execution per-element. Likely not serious, but very easy to avoid. – Matt Enright Jul 27 '11 at 18:40
In C#, we don't write `1==1`, we just write `true`. Also, if he's using L2SQL or L2EF, the runtime probably won't know how to translate this - a better way of writing it, if you *truly* wanted to do it this way, would be `Where(x => x.id == status || status <= 0)` – BlueRaja - Danny Pflughoeft Jul 27 '11 at 19:10
@ BlueRaja - Danny Pflughoeft: You are right (though it works my way also); I'm used to writing `1=1` because of sql background :) – a1ex07 Jul 27 '11 at 19:20

score 1 · Answer 9 · answered Jul 27 '11 at 15:48

Are you attempting to do a conditional LINQ query? if so maybe this would help

var nums = new List<int>() { 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4 };
bool getOdd = true;

var query = nums.AsQueryable();

if (getOdd) {
    query = query.Where(i => i == 1 || i == 3);
} else {
    query = query.Where(i => i == 2 || i == 4);
}

var result = query.ToList();

Is it possible to use a string for a LINQ query expression?

9 Answers9