Jon's Blog

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.

SELECT *
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.
}

One of the first things I like to setup when I am creating a new site is a customized error page and error logging.  To log all unhandled errors on your site you can add some code to the Application_Error event of your Global.asax file.  What I do is to grab the exception and then I use a static method of a helper class I have in my class library.  I pass the name of the web application where the error occurred and the exception itself.  In the helper method I can then extract the details I need from the exception and log it to a database table.

Global.asax.cs:

public const string APP_NAME = "Name of My App";

void Application_Error(object sender, EventArgs e)
{
    Exception ex = Server.GetLastError().GetBaseException();
    SiteErrorLog.LogError(APP_NAME, ex);
}

Next, in the web.config you can setup your site so the user sees a nice, customized error page instead of the default ASP.NET error page.  Under the <system.web> node, add the following:

<customErrors mode="RemoteOnly" defaultRedirect="ErrorPage.aspx"></customErrors>

Now when your site is hit from a remote client (RemoteOnly) and an unhandled error occurs they will be redirected to the page you created (ErrorPage.aspx).  This is much nicer for the user of your site.