Wildcard searches with Linq (Originally published 10/31/07)

How do you do a wildcard search with Linq? 

It took me quite a while to find the answer to this question, so I thought I’d blog it and maybe the search engines will pick it up for the next poor slob who searches the web for an answer.  I don’t know when you’re reading this, but when I did a Google search for "Linq wildcard" I came up empty, even in the groups search.  Seemingly no-one in the world was asking the question I wanted answered.  I’m sure once we’re all using Linq on a daily basis this question will seem very basic, and will be covered in every Linq book on the market.  Right now, though, all the authors are holding on to their books waiting for the RTM before they start selling.  It makes this kind of basic research a bit painful.

So, how do you perform a wildcard search in Linq?  It’s easy enough to write a lambda expression to say that a field must start with, end with, or contain a sub-string, but what if you don’t know which situation you’re going to be facing?  If your users are allowed to type arbitrary, wildcarded values into the UI at runtime, how can you possibly know where they’re going to put the percent signs?  You can’t know this ahead of time, so you’re kind of stuck unless you already know the answer.  That answer is the System.Data.Linq.SqlClient.SqlMethod class, and the Like method specifically.  All you need to do to perform a Like search is something the following:

    if (!string.IsNullOrEmpty(lastName))
        list = list.Where(c => SqlMethods.Like(c.LastName, lastName));

Linq will correctly turn this into a LIKE clause in the resulting SQL, and if the user didn’t happen to put any wildcard characters in the text box, then the results will be the same as a direct comparison search would have returned.  I’m not saying that the execution plan or performance will be the same, I’m not a DBA, but the results of the search will be the same.

Technorati Tags: , ,

This entry was posted in Uncategorized. Bookmark the permalink.

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