Dynamic where clause using Linq To SQL:
Let’s say we need to implement a search method with the following signature:
IEnumerable FindCustomers(string name,string contactName,string city)
If the requirement is that you should be able to pass zero to three arguments to this method and only apply a “where” criteria for the arguments that are not null.
Then we can use the following code to make it work:
IList<Customer> FindCustomers(string name,string contactName,string city)
{
var query = context.Cutomers;
if (name != null)
query = query.Where ( customer => customer.Name == name );
if (contactName != null)
query = query.Where ( customer => customer.ContactName == contactName );
if (city!= null)
query = query.Where ( customer => customer.City == city );
return query.ToList();
}
This way we can pass different combinations of arguments to the method and it will still build the correct where clause that executes at database level.
Do note that this only works when the different criteria should be “AND”‘ed together, but it’s still pretty useful for use cases like the one above.
Roger Johansson Blog 
I rather let the calling layer supply a query object that does this. The calling code could look like this:
var filter = EntityFilter
.Where(c => c.Name == came)
.Where(c => c.City == city);
var customers = FindCustomers(filter);
Your repository could now look like this:
Customer[] FindCustomers(IEntityFilter filter)
{
var query = context.Customers;
query = filter.Filter(query);
return query.ToArray();
}
I wrote about this concept here: http://www.cuttingedge.it/blogs/steven/pivot/entry.php?id=66.
The code for this EntityFilter can be found on CodePlex, here: http://servicelayerhelpers.codeplex.com/SourceControl/changeset/view/32810#537055.
Cheers
You can use the LINQKit’s (http://www.albahari.com/nutshell/linqkit.aspx) Predicate builder to do the OR’s and whatnot. Of course you could also write your own, but it’s pretty.
Thank you some much it helped me a lot and reduced the code lines..