SQL Like Functionality in LINQ?

In this code snippet, we will create an extension method which will add functionality similar to functionality of SQL ‘Like’.

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

Are you going to say ‘No’, wait, just read this snippet and you will say ‘Yes’

First, let's 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 the 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 use ‘StartWith’ and ‘EndWith’ which are predicates, see below:

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

Output of the above will be: Mahesh Chand.

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

Output of the above will be: Mahesh Chand.

In both cases, our output will be the 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 the following namespace you can do 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 the above namespace.

Ah! Unfortunately, here LINQ can't help you.

To solve this problem, we can create an Extension Method as follows:

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 the 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;  
        }  
    }

It's all done!

Now, our Linq queries have functionality equivalent to SQL LIKE.

The post Sql Like functionality in LINQ? appeared first on Gaurav-Arora.com.