In this post, I am going to discuss about the special method available in .NET Framework which allows to perform the like operation as we do in the T-SQL when searching data with string.

In SQL to search string data query is:

--Searching string contains abc i.e. prabcfg, abcpr
Select * from table name where columnname like '%abc%'
--Searching string starts with abc i.e abcpr, abcrana
Select * from table name where columnname like 'abc%'
--Searching string ends with abc i.e prabc, ranaabc
Select * from table name where columnname like '%abc'
--Searching string with wildcard char _ i.e abec,abfc
Select * from table name where columnname like 'ab_c'

Now in LINQ, to achieve the same thing, we have functions like StartsWith, EndsWith and Contains. So LINQ query is:

//Searching string contains abc i.e prabcfg, abcpr
var user = form u in users where u.Name.Contains("abc");
//Searching string starts with abc i.e abcpr, abcrana
var user = form u in users where u.Name.StartsWith("abc");
//Searching string ends with abc i.e prabc, ranaabc
var user = form u in users where u.Name.EndsWith("abc");

But with the LINQ, I am not able to achieve the last case(wildcard char _ ) and many more that I can do in like condition of the T-SQL.

SqlMethod class has a static method Like which allows to perform the same function as the like keyword of T-SQL. So the query with the LINQ is:

var emplist = from emp in dbcontext.Employees
where SqlMethods.Like(emp.FirstName, "pr_nay")
select emp;

When you execute the statement, you can see the T-SQL statement same as above, i.e., wildcard statement listed above, you can view the query in the SQL profiler.
But the Like method works when you do the query using LINQ TO SQL only.

CodeProject