How do I use sql Like functionality in LINQ?

In this code snippet we will just create a simple extension method which provides us a facility to use SQL 'Like' funtionality in LINQ queries

In an interview I have asked a question

'Can we use a 'Like' functionality in LINQ, which contains same functioning as 'Like' in SQL'?

Are you going to say 'No', wait just read this snippet and you will say 'Yes' :)

First, lets take a look how we can achieve the same with 'Contains' apply in LINQ for collections:

var authors = new List<string> { "Gaurav Kumar Arora", "Mahesh Chand", "Shivprasad Koirala", "Sumit Jolly", "Sukesh Marla", "Rj Nitin" };

From above author list, we need to find author 'Mahesh Chand':

//This will return 'Mahesh Chand'
var author = authors.Where(a => a.ToLower().Contains("mahesh"));

Also, we can user 'StartWith' and 'EndWith' which are predicate, see below:

//It will works as: where a LIKE '%manish'
var author = authors.Where(a => a.ToLower().StartsWith("manish"));

Output of above will be: Mahesh Chand

//It will works as: where a LIKE 'manish%'
var author = authors.Where(a => a.ToLower().EndsWith("manish"));

Output of above will be: Mahesh Chand.

In both cases our output will be same as we did not have different data, which meets the conditions.

You are lucky if you are using LINQ-to-Sql or Entity Framework by adding following namespace you can do the everything:

using System.Data.Linq.SqlClient;

and now, it is solved :)

var author = from a in authors
                   where SqlMethods.Like(a, "%/Mahesh/%")
                   select a;

or using Lambda expression:

var author = authors.Where(a => SqlMethods.Like(a, "%/Mahesh/%"));

Think about those conditions where you are going to use complex scenarios and you are not using above namespace.

ah! unfortunately, here LINQ cant help you :(

To solve this problem we can create an Extension Method like follow:

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

I called above as a magic method, now we can use the LIKE functionality as:

var author = from a in authors
                        where a.SqlLike("*mah*chand*")
                        select a;

and using Lambda Expression:

var author = authors.Where(a => a.SqlLike("*mah*chand*"));

Adding one more tweak to this:

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

Now, we can do this:

var author = authors.SqlLike("*mah*chand*");

Here is complete extension class:

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

        public static IEnumerable<string> SqlLike(this IEnumerable<string> enumerable, string expression)
        {
           return enumerable.Where(s => s.SqlLike(expression));
        }

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

Its all done!

Now, our Linq queries are having functionality equivalent to sql LIKE.