linq to sql query with multiple where parameters

asp.net-mvc, c#, entity-framework, linq, sql

Solution

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

Problem

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.

Original source