Click here to Skip to main content
15,510,280 members
Articles / Programming Languages / SQL
Technical Blog
Posted 12 Aug 2015

Tagged as

Stats

12.2K views
10 bookmarked

SQL Like Functionality in LINQ?

Rate me:
Please Sign up or sign in to vote.
4.94/5 (13 votes)
12 Aug 2015CPOL1 min read
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:

C#
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':

C#
//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:

C#
//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.

C#
//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:

C#
using System.Data.Linq.SqlClient;

and now, it is solved.

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

or using Lambda expression:

C#
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:

C#
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:

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

and using Lambda Expression:

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

Adding one more tweak to this:

C#
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:

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

Here is complete extension class:

C#
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
India India
There is no age limit for learning and its never late, to start.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun16-Sep-15 21:06
Humayun Kabir Mamun16-Sep-15 21:06 
AnswerRe: My vote of 5 Pin
Gaurav Aroraa16-Sep-15 23:12
professionalGaurav Aroraa16-Sep-15 23:12 
QuestionMy vote of 5 Pin
Pradeep Shet12-Sep-15 20:30
Pradeep Shet12-Sep-15 20:30 
AnswerRe: My vote of 5 Pin
Gaurav Aroraa13-Sep-15 1:00
professionalGaurav Aroraa13-Sep-15 1:00 
AnswerPlease add correct tags (c#, LINQ) ect... Pin
Liju Sankar12-Sep-15 16:06
professionalLiju Sankar12-Sep-15 16:06 
GeneralRe: Please add correct tags (c#, LINQ) ect... Pin
Gaurav Aroraa13-Sep-15 1:01
professionalGaurav Aroraa13-Sep-15 1:01 
Dev_007 wrote:
Please correct the tags so that this article reached all required audiences


It was updated a week ago (C#, LINQ, HowTo tags), awaiting for approval
Gaurav Arora
http://gaurav-arora.com

GeneralMy vote of 5 Pin
Mahsa Hassankashi12-Sep-15 10:50
Mahsa Hassankashi12-Sep-15 10:50 
AnswerRe: My vote of 5 Pin
Gaurav Aroraa12-Sep-15 10:52
professionalGaurav Aroraa12-Sep-15 10:52 
SuggestionCorrect html tags Pin
Shuby Arora12-Aug-15 21:21
Shuby Arora12-Aug-15 21:21 
AnswerRe: Correct html tags Pin
Gaurav Aroraa13-Aug-15 5:05
professionalGaurav Aroraa13-Aug-15 5:05 
GeneralRe: Correct html tags Pin
Shuby Arora14-Aug-15 10:33
Shuby Arora14-Aug-15 10:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.