ﻁ DevExpertise » Blog Archive » Implementing a LINQ version of SQL’s LIKE Operator

DevExpertise

Practical tips and tricks for all things .NET, SharePoint, Silverlight, InfoPath, and general application development.

comparaison viagra levitra cherche viagra en pharmacie acheter du viagra en pharmacie viagra commande par cheque acheter levitra a andorre site vente de kamagra fiable acheter kamagra via paypal pasteque viagra livraison viagra suisse viagra pharmacie paris acheter viagra authentique viagra sécurité sociale levitra générique forum acheter viagra sur internet achat kamagra inde
    cialis interdit belgique achat securise de viagra ou cialis prix acheter cialis 20mg achat cialis pas cher en france viagra remboursement viagra generique 48 h chrono viagra luxembourg viagra achat en belgique generique du viagra inde europe viagra cialis le vrai prix viagra pharmacie viagra vente libre france pharmacie levitra cialis medicament

Implementing a LINQ version of SQL’s LIKE Operator

Posted by DevExpert on September 25th, 2009

One of the requirements of one of my recent projects was to implement a search page which allowed the user to enter a search term that supported wildcards.  The search term could contain any number of wildcards in any position within that term.

If you’ve done anything like this before, you probably know there’s nothing built-in to LINQ that supports this type of behavior.  Sure, you could use a combination of String.StartsWith, String.EndsWith, or String.Contains, but this could quickly become too cumbersome if there are many wildcards and/or they are scattered throughout the search term.  Let’s look at a couple simple examples to illustrate…

Pretend for a second I was doing this in SQL, and I needed to get all values that start with the letter T.  I would do this:

select * from SomeTable where SomeField LIKE 'T%'


The .NET/LINQ equivalent would be this:

var results = (from v in values where v.StartsWith("T") select v);


Not too difficult.  However, what if you wanted to do the SQL-equivalent of this:

select * from SomeTable where SomeField LIKE '%a%a%'


You’d have do a little creative parsing.  It gets even worse when you as the developer doesn’t know what search term will be entered, how many wildcards will be included, and where in the term they appear.  It all has to be dynamic.

I did a little poking around to see if anyone has done this before, and the only thing I could find was recommendations on using StartsWith/EndsWith/Contains, which I already ruled out.  I also found the SqlMethods.Like() method which sounded perfect.  However after further research, discovered it can only be used on an entity directly retrieved from a DataContext, such as this:

using (DemoDataContext db = new DemoDataContext()){
    var results = (from v in db.SomeTable where SqlMethods.Like(v.SomeField, "*a*a*") select v);
}


If you try to use the SqlMethods.Like() method on anything except a DataContext’s Table<T>, you’ll get the following message:

“Method ‘Boolean Like(System.String, System.String)’ cannot be used on the client; it is only for translation to SQL.”

So much for that.  I decided to write my own extension method.  I figured I could write one fairly easily using a regular expression, and I was right!  I checked out a trusty RegEx cheat sheet and found the following relevant metacharacters:

  • ^     Indicates the start of a string
  • $     Indicates the end of a string
  • *     Indicates zero or more of previous expression

Knowing this, I wrote the following extension method:

public static bool Like(this string value, string term) {
    Regex regex = new Regex(string.Format("^{0}$", term.Replace("*", ".*")), RegexOptions.IgnoreCase);
    return regex.IsMatch(value ?? string.Empty);
}


Which I can then use like this:

var results = (from v in values where v.Like("*a*a*") select v);


I can even simplify this by wrapping it up in another extension method:

public static IEnumerable<string> Like(this IEnumerable<string> source, string expression) {
    return (from s in source where s.Like(expression) select s);
}


Now all I have to do is the following:

var results = values.Like("*a*a*");


Finally, a quick usage example to prove it works:

var values = new List<string>(){
    "Widget", "Gadget", "Whatchamacallit", "Gizmo",
    "Thingamabob", "Thingamajig", "Doodad", "Doohickey"};

var results = values.Like("*a*a*");

foreach (string result in results) {
    Console.WriteLine(result);
}


Which outputs:

image

 

Hopefully you’ll find this useful!

3 Responses to “Implementing a LINQ version of SQL’s LIKE Operator”

  1. Me Says:

    Very useful! Thanks for posting.

  2. Machi Says:

    Perfect! Exactly what I was looking for :)
    Thanks a lot!!

  3. Suri Says:

    used it to create a custom method in place of DataView.RowFilter which does not accept wildcards in the middle of the string. Thank you.

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>