LINQ and Dynamic Predicate Construction at Runtime

• Download demo - 16 KB

Introduction

I'm a big believer in practical examples. So, soon enough, I'll get to one, hold on. Before I do though, I have to admit, when I first saw LINQ-to-SQL, I was skeptical quite a bit. I was thinking, "Wow, more syntactic sugar. So it doesn't feel like I'm querying anything when I query SQL. big deal." After all, I'd seen that before.

What I wasn't prepared for was the fact that Microsoft had created a crafty little masterpiece. LINQ basically allows each one of us to extend the language, in a certain manner of speaking. We can take advantage of .NET's JIT-like reflexes (sorry, yes the pun with CAT-like is easy to miss) by constructing trees of LINQ Expressions.

That sounds quite a bit fancier than it is, but the short of it means that I can remove a combinatorial level of manual query writing when I want to combine predicates (tests for rows inclusion in the result set) based on run-time user input.

Background

Using SQL Server is great, as long as you have a great understanding of the T-SQL language, and are relatively comfortable with writing it as needed. There are various shortcuts, such as ORM frameworks that make it conceptually easier to work with SQL. That's especially true when writing it out longhand isn't your cup of tea. But when it really comes down to it, anything beyond object persistence in most of these ORM tools is potentially painful, and usually requires some manual SQL code intervention.

SQL is a great back-end for web apps. In a corporate (politically correctly, read 'Enterprise') environment, you like to be able to put out some kind of application that is useful for other corporate employees. One of the things they might like to do would be searching databases. Now, for you, the SQL guru, that's no big deal. When you want to let someone else at it, you really have to make sure you've covered all the bases and tied up all the loose ends (and used too many buzzwords). I know it's a little vague so far, so, how about that example?!?

Finally, the Example

Let's pretend that you're the IT shop's in house developer. You have the big manager, Sallie, from HR, who'd like to be able to search all of the employees based on any of the available criteria in the corporate database (which so happens to look a lot like the Northwind database included with SQL Server).

That doesn't sound so hard, at first. You have an Employees table and you just need to search the fields in there. No problem... So you make a nicely styled, well oiled (and colored) app in the Visual Studio Designer in which you select which of the 18 fields you'd like to search. You've wired up a text box to supply a value, used as a parameter in a SQL Select, to data-bind the results grid to either of 18 SqlDataSources (one for each field.)

Sallie liked the idea, when you presented it, but says she really needs to be able to query based on multiple columns -- in any order and possibly using them all.

Well, that shoots the data-binding solution in the foot. Looks like you'll have to code this up with a Command pattern... So you allow the user to pick, from a check box list, any of the fields that will be included in the query, and to specify the criteria. You can do a bunch of switch-ing if logic to figure out what kind of query to render by adding a piece of it at a time, or... (of course, you knew I'd get to this) you can use lambdas and the PredicateBuilder to inject multiple predicates into an expression tree!

Now, let's be clear. I didn't invent the PredicateBuilder; in fact, it's quite a popular little doodad brought to the world by Joseph Albahari. You can see it for free here, or check it out in its natural habitat, the LINQKit, with a few other gadget-like utility classes in the same vein.

Solving this problem without LINQ and the PredicateBuilder basically requires the writing of a query variation for every possible combination of columns, or a way to build a query that works out to the same effect. Compiler writers scoff at the thought of this being considered difficult, but for the rest of us, there's only one way to build and walk a tree that compiles to some other kind of code (SQL, in this case) - have someone else do it.

Using the Code

The first notable feature of this example is the user interface. A user will insist that a user interface is all there is in an application. They often refer to a 'feature of the app' as a 'screen', so getting this part set up reasonably is pretty important.

Since this solution requires that the user be able to select any or all of the columns, we like the idea of a checkbox for inclusion of a filter. Most of the fields in question here are nvarchars so we can use a String.Contains() lambda for them. The DateTime field, the birth date, requires the use of value range comparison, so that filter will have to include a lambda that tests a date for betweeness (and also two valid date inputs).

So the individual filter lambdas look like this:

// This kind of lambda, when evaluated,
// will generate a LIKE clause with wildcards on both sides 
e => e.FirstName.Contains(filterFirstName.Text)

// This kind of lambda, when evaluated,
// will generate two value comparisons, just like it looks here
e => e.BirthDate.Value >= startDateRange && e.BirthDate.Value <= endDateRange

That's great, you might say, but how do I combine them?

There's two ways to do this, you can do it client side, which means that you're loading all the data from SQL and filtering on the web server end. You create an array of lambdas, looping through each one, and applying it as a filter to the IQueryable as a Where condition. That can be expensive in server memory. Or you can do it the right way, using PredicateBuilder. Basically, LINQ's Where extension to IEnumerable<T> takes a conditional expression as a parameter. This can be a simple lambda, or this can be a complex combination of many lambdas, or other valid LINQ expressions. The PredicateBuilder just makes it easy to combine an undetermined number of them.

From the sample code:

public IQueryable<Employee> PrepareDataSource() {

    // we start with .True, as in, no filters, get all of them.
    var predicate = PredicateBuilder.True<Employee>();


    // just inspect the 'checks'... throw in some lambdas

    int emplId = -1;

    // use tryparse to make sure we don't run a bogus query.
    if (cbxUseEmployeeID.Checked &&
        int.TryParse(filterEmployeeId.Text, out emplId) &&
        emplId > 0) {

        // here's how simple it is to add a condition to the query.
        // Still not executing yet, just building a tree.
        predicate = predicate.And(e => e.EmployeeID == emplId);
    }

    if (cbxUseLastName.Checked &&
        !string.IsNullOrEmpty(filterLastName.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.LastName.Contains(filterLastName.Text));
    }

    if (cbxUseFirstName.Checked &&
        !string.IsNullOrEmpty(filterFirstName.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.FirstName.Contains(filterFirstName.Text));
    }

    if (cbxUseTitle.Checked &&
        !string.IsNullOrEmpty(filterTitle.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.Title.Contains(filterTitle.Text));
    }

    // default value to avoid 'unassigned use' errors. 
    DateTime startDateRange = new DateTime();
    DateTime endDateRange = new DateTime(); 

    if (cbxUseBirthDate.Checked &&
        DateTime.TryParse(filterBirthDateStart.Text, out startDateRange) &&
        DateTime.TryParse(filterBirthDateEnd.Text, out endDateRange)) {

        // tack on some numeric range testing. I'd have
        // liked to do a between query, but I don't know if
        // there is one that translates in such a way, so we'll just do this:

        predicate = predicate.And(e => e.BirthDate.Value >= 
            startDateRange && e.BirthDate.Value <= endDateRange);
    }

    if (cbxUseAddress.Checked &&
        !string.IsNullOrEmpty(filterAddress.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.Address.Contains(filterAddress.Text));
    }

    if (cbxUseCity.Checked &&
        !string.IsNullOrEmpty(filterCity.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.City.Contains(filterCity.Text));
    }

    if (cbxUseState.Checked &&
        !string.IsNullOrEmpty(filterState.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.Region.Contains(filterState.Text));
    }
    

    if (cbxUsePostalCode.Checked &&
        !string.IsNullOrEmpty(filterPostalCode.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.PostalCode.Contains(filterPostalCode.Text));
    }

    if (cbxUseCountry.Checked &&
        !string.IsNullOrEmpty(filterCountry.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.Country.Contains(filterCountry.Text));
    }

    if (cbxUseHomePhone.Checked &&
        !string.IsNullOrEmpty(filterHomePhone.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.HomePhone.Contains(filterHomePhone.Text));
    }

    if (cbxUseNotes.Checked &&
        !string.IsNullOrEmpty(filterNotes.Text)) {

        // this translates into a LIKE query.
        predicate = predicate.And(e => e.Notes.Contains(filterNotes.Text));
    }

    var results = Config.GetCurrentContext().Employees.Where(predicate);

    // If you are debugging, you can put a breakpoint
    // up there and see the Query by hovering over 'results'.

    // this query is constructed but not yet exectued
    return results;
}

It's so easy with the PredicateBuilder, I think they're trying to make it illegal in several states. (I'd say that is nearly akin to the so-called 'mechanical engineers' getting upset about programmers being called 'software engineers'.)

Points of Interest

If you look in the download at the class Employee, you'll notice that I'm taking advantage of the partial nature of the objects that LINQ-to-SQL creates. I added a static method that returns the entire IQueryable<Employee> in the database. Notice, I also tagged the partial class with a System.ComponentModel.DataObjectAttribute. I did this so I could populate the columns in the GridView by binding it temporarily to an ObjectDataSource using the GetAll() as the Select method. All the columns appear, I delete the ObjectDataSource, and go on my merry way. Saved myself a little typing.

Something you may not know is that LINQ-to-SQL is very intelligent. Yes. Have you ever thought, hmmm this would best be served by a SELECT Foobar where Baz in ('some', 'list', 'of', 'elements'), and then thought better of it because obviously LINQ can't generate that? If you have, you'd have been wrong(!), because as it turns out, it can grow it just about the same way that you would. I don't use it in the code download... but see here:

string [] values = new string[] { "Leverling", "Davolio", 
             "Callahan", "Dodsworth" };

// No WAY that'll work! 
predicate = predicate.And(e => values.Contains(e.LastName));

// Yes WAY! It gets translated to  AND Employees.LastName
// in ('Leverling', 'Davolio', 'Callahan', 'Dodsworth')

I did a little bit of math, and I figured out that if I were to write all possible combinations of the 18 fields in the Employees table combinatorially, I would have to construct 262,142 unique SQL statements to accommodate each one. That's not to mention an if statement and a condition block for each! (I know, nobody really does it that way, I hope.)

If you were to write a query builder of your own, you'd have to do a little bit of compiler-like translation. I know, from extensive experience writing software that performs custom tree-based syntactic translation, there are corner cases that you wouldn't expect, and those are what take the longest to find and fix.

The moral of the story is, when you absolutely need flexibility (not necessarily speed) of behavior, runtime translation is the way to go. LINQ-to-SQL is a nice, accessible, available, ready-made solution. And when someone else has already written something that does that for you, use it. (The author looks at you, while he points at 'LINQ-to-SQL'.) Don't roll your own, if you don't have to.

History

• August 14, 2008 - First release. (Northwind DB not included with the code.)
• Later that day, fixed some 'late night' grammar mistakes and spelling errors.