Post
Ask Question

How do I use sql Like functionality in LINQ?

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:
 
  1. 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':
 
  1. //This will return 'Mahesh Chand'  
  2. var author = authors.Where(a => a.ToLower().Contains("mahesh"));  
Also, we can user 'StartWith' and 'EndWith' which are predicate, see below:
 
  1. //It will works as: where a LIKE '%manish'  
  2. var author = authors.Where(a => a.ToLower().StartsWith("manish"));  
Output of above will be: Mahesh Chand
 
  1. //It will works as: where a LIKE 'manish%'  
  2. 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:
 
  1. using System.Data.Linq.SqlClient;  
and now, it is solved :)
 
  1. var author = from a in authors  
  2.                    where SqlMethods.Like(a, "%/Mahesh/%")  
  3.                    select a;  
or using Lambda expression:
 
 
  1. 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:
 
  1. public static class SqlExtensionMethod  
  2.     {  
  3.         public static bool SqlLike(this string value, string likeTerm)  
  4.         {  
  5.             var regex = new Regex(string.Format("^{0}$", likeTerm.Replace("*", ".")), RegexOptions.IgnoreCase);  
  6.             return regex.IsMatch(value ?? string.Empty);  
  7.         }  
  8.     }  
 
I called above as a magic method, now we can use the LIKE functionality as:
  1. var author = from a in authors  
  2.                         where a.SqlLike("*mah*chand*")   
  3.                         select a;  
and using Lambda Expression:
 
  1. var author = authors.Where(a => a.SqlLike("*mah*chand*"));  
Adding one more tweak to this:
 
  1. public static IEnumerable<string> SqlLike(this IEnumerable<string> source, string expression)  
  2.         {  
  3.             return from s in source   
  4.                    where s.SqlLike(expression)   
  5.                    select s;  
  6.         }  
Now, we can do this:
 
  1. var author = authors.SqlLike("*mah*chand*");  
 Here is complete extension class:
 
  1. public static class SqlExtensionMethod  
  2.     {  
  3.         public static bool SqlLike(this string value, string likeTerm)  
  4.         {  
  5.             var regex = new Regex(string.Format("^{0}$", likeTerm.Replace("*", ".")), RegexOptions.IgnoreCase);  
  6.             return regex.IsMatch(value ?? string.Empty);  
  7.         }  
  8.   
  9.         public static IEnumerable<string> SqlLike(this IEnumerable<string> enumerable, string expression)  
  10.         {  
  11.            return enumerable.Where(s => s.SqlLike(expression));  
  12.         }  
  13.   
  14.         public static IEnumerable<string> SqlLike_Old(this IEnumerable<string> enumerable, string expression)  
  15.         {  
  16.             return from s in enumerable  
  17.                    where s.SqlLike(expression)  
  18.                    select s;  
  19.         }  
  20.     }  
 
 
Its all done!
 
Now, our Linq queries are having functionality equivalent to sql LIKE.