Jon's Blog

.NET Development & More

LINQ: Complicated Advanced Search with Dynamic Where Clause

Recently I was working a project that needed a very complicated Advanced Search.  I had to dynamically build the Where clause.  The tricky part for this project was that the Where statement would be using both SQL ORs and ANDs.  Using LINQ, the prior way I was building dynamic Where statements was just using ANDs.  Basically I was just adding AND statements if they needed to be included in the query.  However, this time I needed to include a clause where the user could pick one or more of an item from a list.  Thankfully, I discovered the PredicateBuilder class (part of the LINQKit library), which allows you to dynamically build a Where statement with both ANDs and ORs.

I have put together a very simple and crude example to summarize what I did.  Please excuse the simplicity of the code below.  Usually I would write the code much cleaner, but I wanted to keep it simple for readability. 

So let's say you have an advanced restaurant search form.  The user must select one or more cuisine types and then can optionally choose the city, state, country where the restaurant is located.  The form might look like this:

The query I was building in my project was more complicated than this, but you get an idea of how complicated these kinds of queries can get. Here's some example SQL of what LINQ might need to generate.

FROM Restaurant
WHERE City='Dallas'
AND State='TX'
AND(Mexican=1 OR German=1 OR Italian=1)


With the PredicateBuilder class this is totally doable.  Following is the button click event for the Search button where you can get an idea of how to use PredicateBuilder.  First, you build the inner OR statement and then you can add that to your full Where clause at the end.  This is just a quick example, so go read the documentation for a better understanding of how PredicateBuilder works.

using LinqKit;

protected void btnSearch_Click(object sender, EventArgs e)
    // Create Inner Where Statements
    var innerWhere = PredicateBuilder.False<Restaurant>();

    if (cbMexican.Checked)
        innerWhere = innerWhere.Or(q => q.Mexican);

    if (cbItalian.Checked)
        innerWhere = innerWhere.Or(q => q.Italian);
    if (cbChinese.Checked)
        innerWhere = innerWhere.Or(q => q.Chinese);

    if (cbGerman.Checked)
        innerWhere = innerWhere.Or(q => q.German);

    if (cbAmerican.Checked)
        innerWhere = innerWhere.Or(q => q.American);

    // Build Full Where Statement
    var outerWhere = PredicateBuilder.True<Restaurant>();

    if (!string.IsNullOrEmpty(ddlCountry.SelectedValue))
        outerWhere = outerWhere.And(q => q.Country == ddlCountry.SelectedValue);

    if (!string.IsNullOrEmpty(ddlState.SelectedValue))
        outerWhere = outerWhere.And(q => q.State == ddlState.SelectedValue);

    if (!string.IsNullOrEmpty(txtCity.Text.Trim()))
        outerWhere = outerWhere.And(q => q.City == txtCity.Text.Trim());

    // Add the inner OR query to the Where statement
    outerWhere = outerWhere.And(innerWhere);

    var query = from r in myDC.Restaurants.Where(outerWhere)
                select r;

    // Bind query to ListView, GridView, etc.


LINQ Advanced Search

When creating an advanced search you often need to dynamically create the Where statments in your SQL.  Here is an easy way to do this in LINQ.  In the example below orderID, lastName, firstName, shipped are all values retrieved from controls on the page.

var query = from orders in dataContext.MyOrdersTable
select orders;

if (orderID.HasValue)
query = query.Where(order => order.OrderID == orderID);
if (!string.IsNullOrEmpty(lastName))
query = query.Where(order => order.LastName == lastName);
if (!string.IsNullOrEmpty(firstName))
query = query.Where(order => order.FirstName == firstName);
if (shipped.HasValue)
query = query.Where(order => order.Shipped == shipped);

myGridView.DataSouce = query;