Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Like operator in LINQ to Objects

I'm trying to emulate the LIKE operator in LINQ to Objects. Here my code:

List<string> list = new List<string>();
list.Add("line one");
list.Add("line two");
list.Add("line three");
list.Add("line four");
list.Add("line five");
list.Add("line six");
list.Add("line seven");
list.Add("line eight");
list.Add("line nine");
list.Add("line ten");

string pattern = "%ine%e";

var res = from i in list
            where System.Data.Linq.SqlClient.SqlMethods.Like(i, pattern)
              select i;

It did not get me result because of System.Data.Linq.SqlClient.SqlMethods.Like is only for translation into SQL.

Does anything similar to sql LIKE operator exists in LINQ to Objects world?

like image 534
Anton Semenov Avatar asked Apr 14 '11 13:04

Anton Semenov


2 Answers

I don't know of one that readily exists, but if you're familiar with regular expressions, you can write your own:

using System;
using System.Text.RegularExpressions;

public static class MyExtensions
{
    public static bool Like(this string s, string pattern, RegexOptions options = RegexOptions.IgnoreCase)
    {
        return Regex.IsMatch(s, pattern, options);
    }
}

And then in your code:

string pattern = ".*ine.*e";
var res = from i in list
    where i.Like(pattern)
    select i;
like image 133
jvstech Avatar answered Oct 03 '22 03:10

jvstech


This snippet will mimic the behavior and syntax of Sql LIKE. You can wrap it up into a lambda or extension method of your own for use within a Linq statement:

public static bool IsSqlLikeMatch(string input, string pattern)
{
   /* Turn "off" all regular expression related syntax in
    * the pattern string. */
   pattern = Regex.Escape(pattern);

   /* Replace the SQL LIKE wildcard metacharacters with the
    * equivalent regular expression metacharacters. */
   pattern = pattern.Replace("%", ".*?").Replace("_", ".");

   /* The previous call to Regex.Escape actually turned off
    * too many metacharacters, i.e. those which are recognized by
    * both the regular expression engine and the SQL LIKE
    * statement ([...] and [^...]). Those metacharacters have
    * to be manually unescaped here. */
   pattern = pattern.Replace(@"\[", "[").Replace(@"\]", "]").Replace(@"\^", "^");

   return Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);
}

A roughed together extension method that would work like IEnumerable<T>.Where method:

public static IEnumerable<T> Like<T>(this IEnumerable<T> source, Func<T, string> selector, string pattern)
{
   return source.Where(t => IsSqlLikeMatch(selector(t), pattern));
}

Which would in turn allow you to format your statement like so:

string pattern = "%ine%e";
var res = list.Like(s => s, pattern);

EDIT An improved implementation, should anyone stumble upon and want to use this code. It converts and compiles the regex once instead for each item and the conversion from LIKE to regex above has some bugs.

public static class LikeExtension
{
    public static IEnumerable<T> Like<T>(this IEnumerable<T> source, Func<T, string> selector, string pattern)
    {
        var regex = new Regex(ConvertLikeToRegex(pattern), RegexOptions.IgnoreCase);
        return source.Where(t => IsRegexMatch(selector(t), regex));
    }

    static bool IsRegexMatch(string input, Regex regex)
    {
        if (input == null)
            return false;

        return regex.IsMatch(input);
    }

    static string ConvertLikeToRegex(string pattern)
    {
        StringBuilder builder = new StringBuilder();
        // Turn "off" all regular expression related syntax in the pattern string
        // and add regex begining of and end of line tokens so '%abc' and 'abc%' work as expected
        builder.Append("^").Append(Regex.Escape(pattern)).Append("$");

        /* Replace the SQL LIKE wildcard metacharacters with the
        * equivalent regular expression metacharacters. */
        builder.Replace("%", ".*").Replace("_", ".");

        /* The previous call to Regex.Escape actually turned off
        * too many metacharacters, i.e. those which are recognized by
        * both the regular expression engine and the SQL LIKE
        * statement ([...] and [^...]). Those metacharacters have
        * to be manually unescaped here. */
        builder.Replace(@"\[", "[").Replace(@"\]", "]").Replace(@"\^", "^");

        // put SQL LIKE wildcard literals back
        builder.Replace("[.*]", "[%]").Replace("[.]", "[_]");

        return builder.ToString();
    }
}
like image 37
dkackman Avatar answered Oct 03 '22 03:10

dkackman