Saturday, August 6, 2011

SqlMethod LIKE

Shout it kick it on DotNetKicks.com
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 to 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 same thing we have function 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 cannot able to achieve the last case(wildcard char _ ) and many more that I can do in like condition of the t-SQL.

SqlMehtod class has 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 the t-sql statement same as above i.e wildcard statement listed above, you can view the query in the sql profiler.
But the the Like method work when you do the query using LINQ TO SQL only.

1 comment:

  1. Hi, cool post. I have been thinking about this topic,so thanks for sharing. I will probably be subscribing to your blog. Keep up great writing!!!
    Land Rover Range Rover Supercharger

    ReplyDelete