Linq To Sql: Dynamic Where Clause
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.
RSS-Feed
Design by 
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
Steven
November 21, 2009 at 7:06 pm
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.
dave
January 30, 2010 at 6:55 pm