Dynamic where clause in Linq to Entities

Question

I'm using linq to entities(EF). I have a constructor which takes 4 string parameters. Depending on what parameter is not null I have to build the linq query. I can do with if else statements but i also has other constructor with 10 parameters in that case there will be many combinations to check.

Example:

Constructor(p1,p2,p3,p4)
{
  var prod= from p in ctxt.products.expand("items\details")
            where p.x==p1 && p.xx==p2 && p.xxx==p3 && p.xxxx==p4
            select p;
}

In the above where clause there should be condition checks only if the parameter is not null. ie., if p2 is null then the where clause should look like

where p.x==p1 && p.xxx==p3 && p.xxxx==p4

if p2 and p3 are null then

where p.x==p1 && p.xxxx==p4

Can anyone tell me how to handle this. if possible can you give sample code for this

Possible duplicate of http://stackoverflow.com/questions/697345/linq-to-sql-need-dynamic-where-clause-over-relational-tables-help?rq=1 — Michael Freidgeim, Oct 04 '12 at 18:15

score 11 · Accepted Answer · answered Feb 03 '12 at 00:48

11

Linq's DeferredExecution to rescue. Linq query is not executed unless the data is requested from it.

var prod = from p in ctxt.products.expand("items\details")
        select p;

if (p1 != null)
{
    prod = prod.Where(p => p.x == p1);
}

if (p2 != null)
{
    prod = prod.Where(p => p.xx == p2);
}

// Execute the query

var prodResult = prod.ToList();

answered Feb 03 '12 at 00:48

amit_g

30,880
8
61
118

if we do like this we will be loading large amount of data in prod and then we are taking subset of it. Can we get the subset directly. – Deepak Feb 03 '12 at 01:09
3

The query is **NOT** executed until after the where clause is built. Inn the above code the query is built until the very last line and that is where it is executed. You can ch3eck this by running SQL profiler. – amit_g Feb 03 '12 at 01:15
Yes you are correct. I can use your code and its perfectly working. – Deepak Feb 03 '12 at 01:57
can we use async here? – HamedH Jun 20 '17 at 08:40
Here p1 and p2 are added in the AND condition. Is ther a way to enforce an OR join? – Afshar Mohebi May 13 '19 at 08:04

score 3 · Answer 2 · answered Feb 03 '12 at 00:47

3

You can chain the methods as needed:

 YourType(string p1, string p2, string p3, string p4)
 {
      var prod = ctxt.Products.Expand("items\details");

      if (!p1.IsNullOrWhiteSpace())
          prod = prod.Where(p => p.x == p1);
      if (!p2.IsNullOrWhiteSpace())
          prod = prod.Where(p => p.xx == p2);

      // ....

      // use "prod"
 }

The resulting SQL should be the same as if you put them all in a single statement.

answered Feb 03 '12 at 00:47

Reed Copsey

554,122
78
1,158
1,373

1

if we do like this we will be loading large amount of data in prod and then we are taking subset of it. Can we get the subset directly otherwise we are loading unnecessary data – Deepak Feb 03 '12 at 01:26
1

@Deepak No, you won't. The nice thing about IQueryable is that it doesn't "load the data" - LINQ uses deferred execution, and with EF, it translates the final "query" into a single SQL statement. This will be the same result. – Reed Copsey Feb 03 '12 at 01:32
1

@Deepak For details, see: http://stackoverflow.com/questions/1578778/using-iqueryable-with-linq/1578977#1578977 – Reed Copsey Feb 03 '12 at 01:32
Here p1 and p2 are added in the AND condition. Is ther a way to enforce an OR join? – Afshar Mohebi May 13 '19 at 08:05

score 3 · Answer 3 · answered Feb 03 '12 at 00:49

3

You could always build the query in pieces and take advantage of delayed query execution:

public Constructor(int? p1, int? p2, int? p3, int? p4)
{
    var prod = ctxt.products.expand("items\details");

    if(p1 != null)
        prod = prod.Where(p.x == p1);

    if(p2 != null)
        prod = prod.Where(p.xx == p2);

    if(p3 != null)
        prod = prod.Where(p.xxx == p3);

    if(p4 != null)
        prod = prod.Where(p.xxxx == p4);
}

answered Feb 03 '12 at 00:49

Justin Niessner

242,243
40
408
536

if we do like this we will be loading large amount of data in prod and then we are taking subset of it. Can we get the subset directly otherwise we are loading unnecessary data – Deepak Feb 03 '12 at 01:26
2

@Deepak - You won't be loading any data at all. LINQ doesn't execute the query until you actually use the data. By the time the query executes, you'll already have the proper where clauses in place to load only the data you need. – Justin Niessner Feb 03 '12 at 04:44
Here p1 and p2 are added in the AND condition. Is ther a way to enforce an OR join? – Afshar Mohebi May 13 '19 at 08:05

Dynamic where clause in Linq to Entities

3 Answers3

Linked