ﻁ 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.

http://www.isg.rhul.ac.uk/akay/teaching/... madrid xxl comprar kamagra
cialis achat comparatif http://gev.industrie.gouv.fr/CACHE/well/... http://www.redovie.u-psud.fr/?Nty=1&Ntt=... acheter cialis noir
prix de viagra viagra inde acheter
  • cialis 20mg preisvergleich viagra ersatz hausmittel venta levitra
  • priligy 60 mg kamagra bijwerkingen cialis bijsluiter
    cialis apothekenpreis farmacia online viagra generico http://www.cerphi.net/?size=757124&price... http://www.museovirtual.uanl.mx/media/ar... prijs viagra in apotheek http://www.museovirtual.uanl.mx/media/ar...

    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>