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

Hofmann

This has been asked many times. Please search "dynamic LINQ". – Switzer

It is possible using dynamic linq - see #5139967 – Gaylene

Answer 1

Since LINQ is lazy, you can just do

var query = ...

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

Petrinapetrine

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? – Hofmann

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 – Salvage

@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. – Petrinapetrine

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.. :) – Hofmann

Yep, foreach will force execution of the query as well. – Petrinapetrine

Answer 2

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?

Parrish

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 :( – Hofmann

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. – Parrish

@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. – Elizabethelizabethan

Answer 3

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

Philoprogenitive

Answer 4

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.

Phototelegraph

Where clause not needed at all here. Just use database.MyTable; – Parrish

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 :( – Hofmann

@Hofmann 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. – Kleist

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. – Phototelegraph

Answer 5

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

Gaylene

Answer 6

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

Salvage

Answer 7

AFAIK, you will need to have 2 different queries.

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

Overbite

Answer 8

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

Antipasto

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. – Kleist

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) – Hectogram

@ BlueRaja - Danny Pflughoeft: You are right (though it works my way also); I'm used to writing 1=1 because of sql background :) – Antipasto

Answer 9

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

Salpingitis