Multiple where conditions in EF

Question

Possible Duplicate:
Conditional Linq Queries

Using Entity Framework 4.0

I have a search condition like this

enter image description here

There are four fields that allow the users to filter their search. The conditions are all AND. The result has to omit the corresponding filter if the textbox value is String.Empty or the dropdownlist value is All. Could do this in a Stored Procedure but I am unable to mimic that at all in a Linq2SQL/ Entity Framework scenario.

My question is this, how to omit IEnumerable.Where in the Linq according to some entered values?

http://stackoverflow.com/questions/11194/conditional-linq-queries — Kevin Stricker, Jun 15 '11 at 05:24
You want all the conditions including String.Empty| dropdownList.Value!=-1 conditions fit into linq query? If not, you can make this an external condition and then just fit the filtering conditions into linq query. — Zenwalker, Jun 15 '11 at 05:27
@mootinator: thanks i am currently doing something like that. ia that the only way out? — naveen, Jun 15 '11 at 05:36
why is that a bad thing? I would prefer that over having crazy boolean operators all over the place. — RPM1984, Jun 15 '11 at 05:56
@RPM1984: its not bad at all. was enquiring about better options actually :) — naveen, Jun 16 '11 at 04:00

Chris Woolum · Accepted Answer · 2011-06-15T18:14:12.130

62

You can chain your where clauses. You just need an IQueryable datasource.

var filteredData = _repository.GetAll();
//If your data source is IEnumerable, just add .AsQueryable() to make it IQueryable

if(keyWordTextBox.Text!="")
    filteredData=filteredData.Where(m=>m.Keyword.Contains(keyWordTextBox.Text));

if(LocationDropDown.SelectedValue!="All")
    filteredData=filteredData.Where(m=>m.Location==LocationDropDown.SelectedValue));

... etc....

Because it is IQueryable, the data is not fetched until you bind it so it only pulls the data you need.

edited Jun 15 '11 at 18:14

answered Jun 15 '11 at 06:26

Chris Woolum

2,854
20
20

10

so does the multiple `.Where()` act as an AND between the where. So for example: `db.Where(x=>x.A==true).Where(x=>x.B==true)` is the same as `db.Where(x=> x.A==true && x.B == true)` – Zapnologica Dec 29 '15 at 05:16
10

When you chain Wheres', they always append as an AND. – Chris Woolum Dec 29 '15 at 16:16
8

How about performance? Does splitting conditions across multiple `where` clauses cause overhead? – SepehrM Mar 27 '16 at 17:49
6

No, because it is not actually parsed until the query is materialized. – Chris Woolum Aug 30 '17 at 01:28
Is there a way so that `OR` is used instead of `AND` if I use multiple where? – shashwat Apr 13 '20 at 17:11
6

`Because it is IQueryable, the data is not fetched until you bind it so it only pulls the data you need.` This is a very important line that should be at the top of any tutorial that deals with EF queries! – jamheadart Sep 10 '20 at 13:41

score 8 · Answer 2 · answered Jun 15 '11 at 05:37

Assuming that Location and Category are identified in your code by ids (id is the value attribute in the comboboxes items), you can do something similar to

function GetItems(string keyword, string consultant, int? locationId, int categoryId){

using(MyContextEntities context = new MyContextEntities()){
    return context.Items.Where(item => 
        (string.IsNullOrEmpty(keyword) || item.Text.Contains(keyword))
        && (string.IsNullOrEmpty(consultant) || item.Consultant.Contains(consultant))
        && (!locationId.HasValue || item.Location.Id == locationId.Value)
        && (!categoryId.HasValue || item.Category.Id == categoryId.Value)
    );
}
}

score 8 · Answer 3 · edited Jul 07 '20 at 03:11

Take a look at PredicateBuilder. It will allow you to do something like this:

IQueryable<??> SearchProducts (params string[] keywords)
{
  var predicate = PredicateBuilder.True<??>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    if(temp != String.Empty || temp != "All")
          predicate = predicate.And(e => e.???.Contains (temp));
  }
  return dataContext.??.Where (predicate);
}

Note:

score 2 · Answer 4 · answered Jun 15 '11 at 05:57

2

The flexible way to do this is to build up the where clause separately.

This article shows you how to do that. It takes a bit of work to initially set it up. But its worth it.

answered Jun 15 '11 at 05:57

Eranga

32,181
5
97
96

score 1 · Answer 5 · answered Jun 15 '11 at 05:34

1

You can do something like this.

var abc = from al in myEntity.a
                  where (field == string.Empty ? al.field == string.Empty : al.field == field)
                  select new { al.field1, al.field2, al.field3 };

answered Jun 15 '11 at 05:34

Jits

712
7
11

score -2 · Answer 6 · answered Jun 15 '11 at 05:27

-2

I think Skip While and Take While may help in your situation

http://msdn.microsoft.com/en-us/vcsharp/aa336757#SkipWhileSimple

answered Jun 15 '11 at 05:27

Deepesh

5,346
6
30
45

Multiple where conditions in EF

6 Answers6

Linked