Multiple where conditions in EF [duplicate]

Question

Possible Duplicate:
Conditional Linq Queries

Using Entity Framework 4.0

I have a search condition like this

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?

Answer 1

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.

Answer 2

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

Answer 3

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:

Answer 4

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.