LINQ to SQL Ad-Hoc Searches

One of the nicest features about LINQ to SQL is that it is type safe. It won't compile if I misspell something or have a conversion error. This makes using LINQ to SQL very efficient. However, when queries get complicated, it often becomes very difficult to get the correct query - and to get an optimal query can absolutely impossible. In a previous article, Practical Thoughts on Using ORMs, I recommended created database views whenever queries got complex. But sometimes the complexity is not about the joining, but about the criteria. In this article, I'd like to demonstrate a method for doing ad-hoc searches much like you'd find in Google or any search engine.

Suppose you needed an ad-hoc search interface for the following class:

public class Customer

{
      public string Id { get; set; }
      public string AccountName { get; set; }
      public string AccountNumber { get; set; }
      public string StateCode { get; set; }
}

The users just want to type something that would be used as criteria against multiple fields, such as a list of words. By itself, that may be simple enough. You decide which properties to query against, split the list of words, and create multiple Where statements like so:

IQueryable<Customer> query = from c in Customers

                                select c;

foreach (string criterion in criteria.Split(' '))

{
     query = query.Where(c =>
                    c.AccountName.Contains(criterion) ||
                    c.AccountNumber.Contains(criterion) ||
                    c.StateCode.Contains(criterion));
}

return query.ToList();

 

The problem with this code, however, is that the criteria in the Where clause will all be joined with And conditions. It would be more intuitive if they used OR conditions. Unfortunately, that is not supported in LINQ to SQL out of the box.

 

Never fear, a number of helpful coders have provided an extension library to solve that problem: LINQKit. You can download the binaries and source code at

http://www.albahari.com/nutshell/linqkit.aspx.

What we're most interested in is the PedicateBuilder. There's an example of just what we're doing here in the link above; however, we're going to get a little more complicated.

After referencing the LINQKit assembly and namespace, we get a couple useful extensions. First, there are extension methods for And and Or on Expression<Func<T,bool>>, which is what we're using in the Where clause. Second, in order to make it work, we need another extension method called AsExpandable (note, the documentation suggests this is only necessary for EntityFramework, as opposed to straight Linq-to-SQL.) With these two extensions, our code can be modified thusly:

IQueryable<Customer> query = from c in Customers

                                select c;

Expression<Func<Customer, bool>> searchclause;

foreach (string criterion in criteria.Split(' '))

{

        Expression<Func<Customer, bool>> predicate = c =>

                                        c.AccountName.Contains(criterion) ||

                                        c.AccountNumber.Contains(criterion) ||

                                        c.StateCode.Contains(criterion));

    searchclause = (searchclause==null) ? predicate : searchclause.Or(predicate);

}

query = query.Where(searchclause);

return query.AsExpandable().ToList();

That's great for a simple ad-hoc search form. However, what if I want to provide some more elaborate features, such as explicit AND operator or a string of text in quotes or a NOT operator? To do that, I need to be more sophisticated with the criteria string and how I parse it.

The logic involved is fairly simple but maybe not so obvious; it will make sense once you work through it, though. Below is a complete code for what I just described:

IQueryable<Customer> query = from c in Customers

                               select c;

Expression<Func<Customer, bool>> searchclause = null;

bool useOrCondition = true;

bool useNotCondition = false;

foreach (string criterion in criteria.Split(' '))

{
    string term = criterion;
    if (term.Trim().Length == 0)
    {
        // do nothing
    }
    else if (term.ToUpper() == "AND")
    {
        useOrCondition = false;
    }
    else if (term.ToUpper() == "OR")
    {
        useOrCondition = true;
    }
    else if (term.ToUpper() == "NOT")
    {
        useNotCondition = true;
        useOrCondition = false;
    }
    else
    {
        Expression<Func<Customer, bool>> predicateexpression;
        if (useOrCondition)
        {
            predicateexpression = c =>
                c.AccountNumber.Contains(term) ||
                c.AccountName.Contains(term) ||
                c.StateCode.Contains(term);
            searchclause = (searchclause == null)
                ? predicateexpression : searchclause.Or(predicateexpression);
        }
        else if (useNotCondition)
        {
            predicateexpression = c =>
                c.AccountNumber.Contains(term) &&
                c.AccountName.Contains(term) &&
                c.StateCode.Contains(term);
            searchclause = (searchclause == null)
                ? predicateexpression : searchclause.And(predicateexpression);
        }
        else
        {
            predicateexpression = c =>
                c.AccountNumber.Contains(term) ||
                c.AccountName.Contains(term) ||
                c.StateCode.Contains(term);
            searchclause = (searchclause == null)
                ? predicateexpression : searchclause.And(predicateexpression);
        }

        useOrCondition = true;

        useNotCondition = false;
    }
}
query = query.Where(searchclause);

return query;

There are a couple things that this code does NOT do:

1. It does not handled terms surrounded by quotes.
2. It does not handle operator precedence (if you mix AND and OR the results will not likely be what you expect.
3. It does not handle parentheses.

You could probably expand the code above to handle all these cases with a little effort.