Share via

An idea to add SQL Like Functionality in LINQ

  • Article

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.

Other languages

This wiki article is also available in these languages: