Linq for search queries with multiple optional parameters

Several months ago, I began using Linq on one of our major projects.  Initially, I didn’t really understand how beneficial it would be, but perhaps that was a good lesson to give new things a fair shake before discounting them.  After all, there was probably a good reason for creating it, or they wouldn’t have gone to the trouble.  Anyway, I have been very impressed, to say the least.  The speed with which I can create new queries to get the data that I need is remarkable, indeed.

Reflecting now on what we used to do, I can’t help but quote the old Billy Joel song, “The good old days weren’t always good, and tomorrow ain’t as bad as it seems.”

Invariably, there comes a point where we needed to add a column to a table and then regenerate the Dataset queries.  Most of the time, this was pretty easy, but every once in a while, one of the regenerated queries decided to reorder its parameters, unbeknownst to us.  On a good day, this became immediately obvious because the parameter types were different.  On a bad day, however, which became a bad day even it was a good day, the parameters types would be the same and no compile error would alert us to what had happened.  Those were sometimes difficult to even notice and then even more difficult to find.

Then you had the common search page, where the user may or may not specifiy a myriad of various search items.  This gave rise to either one heck of a Sql query with multiple tests for -1 or a just a lot of individual queries.  Neither option was very pleasant.

SELECT *
FROM ThisTable
WHERE  ((ThisColumn = @ThisColumn) || (ThisColumn = -1))
AND ((ThatColumn = @ThatColumn) || (ThatColumn = -1))
AND (IAmGoingInsane = true)

Of course, this gave rise to major efficiency issues as well in the speed of the search.

Enter Linq. (applause)

This seems obvious to me now, but it didn’t occur to me at first.  One of the nice things about Linq is that it builds the query in the code and doesn’t execute it until you, so you can basically add only those parameters that the user has selected, like this:

var results = (from theStuff in DataContext.Stuffs
where theStuff.NameLast == nameLast);
if (nameFirst != "")
results = results.Where(r => r.NameFirst == nameFirst);
if (city!= "")
        results = results.Where(r => r.City == city);
return results;

Since the code only adds the parameters you need, the Sql query can still be optimized, and you don’t need a bunch of
different queries–just one, with all the parameters in it.

Pretty nice. I’m very happy, indeed, that the good old days of DataSets are gone. Thanks, Linq!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: