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.


  1. Steven says:

    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:

    The code for this EntityFilter can be found on CodePlex, here:


  2. dave says:

    You can use the LINQKit’s ( Predicate builder to do the OR’s and whatnot. Of course you could also write your own, but it’s pretty.

  3. Mrkraju says:

    Thank you some much it helped me a lot and reduced the code lines..

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s