Multiple where conditions in EF

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

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

Method 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:

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

Method 5

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

Method 6

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

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0