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. 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:


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 )

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s