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.

5 thoughts on “Linq To Sql: Dynamic Where Clause”

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s