linq to sql query with multiple where parameters

Question

I'm currently writing a search function in ASP.NET MVC 4 with the Entity Framework. However, I've hit a roadblock for which I can only find "bad" solutions.

My search functions gets back a model which consists of 4 parameters:

String Name
String Street
String Code
String Province

List<Query> query = (from t in Model select t).ToList();

Now I'd like to filter on my search input. However, a user can decide to fill in as many search fields as possible. He can decide to use Name and Street, or Name, Street and Province, or ...

The only real solution I was able to find consists of making my query and IQueryable and check whether or not a field has been filled with an if, then use a .Where to update the query. As this would currently give m 5 queries, I'm wondering on whether or not there is a better solution that I'm missing here.

Thanks for helping me.

Solution 1

If I understand you correct. You might want something like this:

string Name;
string Street;
string Code;
string Province;
var query=(from t in Model select t);
if(Name!=null)
{
    query=query.Where (q =>q.Name==Name);
}
if(Street!=null)
{
    query=query.Where (q =>q.Street==Street);
}
if(Code!=null)
{
    query=query.Where (q =>q.Code==Code);
}
if(Province!=null)
{
    query=query.Where (q =>q.Province==Province);
}
List<Query> ls = query.ToList();

You will have a IQueryable when you add the where statements and when you do the ToList() that sql will execute.

Update

To answer the comment of Luis Hernández. So this is how it works. When you select from the model in this case the collection type is IQueryable. This means that it has not been executed against the database. For the query to execute you need to apply some of the final metod. To tell linq that it will actually do the database call. These are for example

ToList()
FirstOrDefault()
SingleOrDefault()
Single()
First()
ToDictionary() 

So when we append the Where clauses without using ToList(). There is no execution of the query.

Please try the query in LinqPad

Solution 2

Use the Entity filter class you find here : https://servicelayerhelpers.codeplex.com/SourceControl/changeset/view/32810#537055

so first specify your filter and after that just apply it to your query.

Example:

var filter = EntityFilter
.Where(c => c.Name == came)
.Where(c => c.City == city);

var customers = FindCustomers(filter);

Customer[] FindCustomers(IEntityFilter filter)
{
var query = context.Customers;
query = filter.Filter(query);
return query.ToArray();
}

more info on: https://cuttingedge.it/blogs/steven/pivot/entry.php?id=66