Most data-driven applications have need a search function that’s driven off a user submitted list of keywords.  In pre-LINQ days, this using entailed looping through the keywords and building up a string to execute on the data source ( On SQL Server, you would be handing a string off to the EXEC command). 

This approach is less than desirable for several reasons, including:

  • It’s error prone, as it requires fiddling with control characters and correctly escaping them
  • SQL Data Sources are susceptible to SQL Injection
  • There’s no type safety, and the resulting query may not query the right columns, or mismatch data types

While you can limit a few of these issues by using varying techniques ( parameters and stored procedures eliminate some of the security issues in SQL Server ), you’re still stuck with an unwieldy way of generating a string-based statement to query against your data source. 

The LINQ Way

Thanks to LINQ, we can forget about all the issues related to building up a string-based query and use one of the more powerful features of LINQ to our advantage: Deferred Execution. 

Deferred Execution is a feature of LINQ that delays the execution of your query until data is actually requested from it.  Using deferred execution, we can repeatedly modify a query to add additional expressions to it, without paying any data access penalty. 

For example, take the following data source:

List<string> items = new List<string>();

items.Add("Laurie");
items.Add("Joe");
items.Add("Chris");
items.Add("Melissa");

If we query the data as is:

var query = from item in items
            select item;

We would see the following results:

Laurie
Joe
Chris
Melissa
 
 
Now, suppose the user wanted to search by two keywords, “a” and “e”:
// Mimic user input
string[] keywords = new string[] { "a", "e" };

In order to restrict the results, we need to add each keyword to our query:

keywords.ToList().ForEach(keyword =>
            query = from item in query
                    where item.ToLower().Contains(keyword.ToLower())
                    select item
                          );

Or, if you prefer the all-lambda way:

keywords.ToList().ForEach(keyword => query = query.Where(item => item.ToLower().Contains(keyword.ToLower())));

And now, if we look at the results of our query:

var results = from item in query
              select item;

We end up with the following:

Laurie
Melissa

And that’s exactly what we wanted!

In this example I’m only using LINQ to objects, but the results are the same regardless of the provider.  One interesting thing to note is that if you do use LINQ to SQL or LINQ to Entities, the resulting query will look very similar to what you would build by hand using a string-based approach.  While that might make LINQ seem like overhead, keep in mind that LINQ is providing you with compile-time checks for all the issues I stated above ( control character manipulation, SQL Injection, type safety, etc. ).  All very good things to have!

- Colin