Like operator in LINQ to Objects

Question

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?

Answer 1

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;

Answer 2

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();
    }
}

Answer 3

You have to use Regex for the pattern, and then use the extension method Where to iterate and find the matches.

So your code should end up like this:

string pattern = @".*ine.*e$";

var res = list.Where( e => Regex.IsMatch( e, pattern));

If you are unfamiliar with Regex, this reads:

First 0 or more characters (.*) followed by ine (ine) then 0 or more characters (.*) then and e (e), and the e should be the end of the string ($)

Answer 4

1. Using String.StartsWith or String.Endswith

Writing the following query:

var query = from c in ctx.Customers

            where c.City.StartsWith("Lo")

            select c;

will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [Lo%]

which is exactly what we wanted. Same goes with String.EndsWith.

But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name). Using the query

var query = from c in ctx.Customers

            where c.City.StartsWith("L") && c.City.Contains("n")

            select c;

generates the statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L%]
AND      City LIKE [%n%]

which is not exactly what we wanted, and a little more complicated as well.

2. Using SqlMethods.Like method

Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods has a method called Like, that can be used in a Linq to SQL query:

var query = from c in ctx.Customers

            where SqlMethods.Like(c.City, "L_n%")

            select c;

This method gets the string expression to check (the customer's city in this example) and the patterns to test against which is provided in the same way you'd write a LIKE clause in SQL.

Using the above query generated the required SQL statement:

SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L_n%]

Source: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx

Answer 5

I don't know if it exists, but here is an implementation of an extension method using Knuth-Morris-Pratt algorithm I made.

public static IEnumerable<T> Like<T>(this IEnumerable<T> lista, Func<T, string> type, string pattern)
            {

                int[] pf = prefixFunction(pattern);

                foreach (T e in lista)
                {
                    if (patternKMP(pattern, type(e), pf))
                        yield return e;
                }

            }

            private static int[] prefixFunction(string p)
            {


                int[] pf = new int[p.Length];
                int k = pf[0] = -1;


                for (int i = 1; i < p.Length; i++)
                {
                    while (k > -1 && p[k + 1] != p[i])
                        k = pf[k];

                    pf[i] = (p[k + 1] == p[i]) ? ++k : k;
                }
                return pf;

            }

            private static bool patternKMP(string p, string t, int[] pf)
            {

                for (int i = 0, k = -1; i < t.Length; i++)
                {

                    while (k > -1 && p[k + 1] != t[i])
                        k = pf[k];

                    if (p[k + 1] == t[i])
                        k++;

                    if (k == p.Length - 1)
                        return true;    
                }

                return false;

            }