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

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.

Hi Dave,
I've got your sample running fine but when I try to implement my own project doing exactly the same things the where clause is not getting appended to the select statement. I've gone through debug and see that the predicate variable is correctly populated
+ predicate {f => (True Or Invoke(e => e.City.Contains(value(ASP.regsearch_aspx).txtCity.Text),f))} System.Linq.Expressions.Expression<System.Func<RegAdmin.RegSearch,bool>>

that's not enough to determine your problem. I can't tell you what's wrong with your code from just that line, because it's obviously something to do with how you're constructing the predicate. Perhaps you didn't start with the .True<something> or .False<something>. gotta do that.

you aren't specifying the type you're querying against. The True() must actually be True<sometypegoeshere> I can't tell what kind of object you have in your code at all. You may have to find out what type the stored procedure is compiled to output. Then just declare your type as IQueryable<whateverthattypeis>. I can't remember off the top of my head how it generates that for stored procedures, but it's some long winded type name Just look at the generated code from your dbml, and find tha t function, and see what type it returns. Then just do it against that type, instead of just leaving the type out altogether.

you'll have to do PredicateBuilder.True <ISingleResult <usp_SALEINFO_getresult > > The whole thing. It's like a data table. I multiple result is actually like a dataset (multiple tables.) The name of the interface is confusing.

You have to "using System.Data.Linq", then you can use ISingleResult :P You will not get away without using it. period. you must use ISingleResult. The class will be something like: ISignleResult<NameOfTheStoredProcResult>. You don't just do ISingleResult<NameOfSToredPRoc>

As long as you are ANDing your search conditions together, this syntax works in exactly the same manner, as you are just building an expression tree for each call to Where(), that will be evaluated when the query is enumerated.

If you need to OR conditions together outside of one Where() then PredicateBuilder becomes useful, as it easily lets you OR existing predicates together (something that is more complex to do using the LINQ API directly).

Yes you're correct, I was thinking in the abstract as in, for any possible conditions, but I only picked examples using the AND. I didn't even notice that until just now when you've pointed it out. I originally had set out to make a radio next to each selecting 'or' vs 'and' but I guess I neglected to put that bit in before publish time. Anyway, you're right.

Another thing you could do change the contains to a SqlMethods.Like for more complicated like clauses.

But thanks for the article though, over the last week I have been trying to find examples of how to dynamically combine (i.e, include/exclude) static where predicates.
Most blogs on Dynamic LINQ talk about dynamically creating the where predicate itself which is a level beyond what I am after.
Of course I came across PredicateBuilder but their website only has snippets but your example shows exactly the scenario I want to achieve - searching on user selected criteria.

Well as for my timing, I posted this round about 2am my time. It was fun making it, but at the end I was starting to get a little groggy. Not that my sentence structure would be much more reasonable when I'm NOT tired, but I do tend to overuse parenthetical interjections when I'm starting to get zoned out

I just found this topic to be so incredibly useful to me that I would like to let others in on it. Maybe it's obvious, I don't know, but it didn't strike me that way when I found it.