LinQ Query With Like Operator

Linq To SQL makes a developer’s life easy for performing various database operations. If the database design is stable, LinQ To SQL will do all the jobs for you for maintaining consistency between data relations. Simple data retrieval, insertion, deletion, update etc can be done in a very easy way , in some cases just by calling some functions on the LinQ objects. There are a number of ways are also provided for using functionality of a SQL operators/keywords. I will try to provide as much examples as possible on them time to time. For today, My main concern is to make you familiar with the ‘Like’ keyword and construct a easy alternative of SQL query with Like and LinQ together.

‘Like’ is a popular SQL syntax operator, that is used widely for performing search operations on database tables. Thanks To LinQ To SQL, we can now get this functionality with help of few ready given functions or even query syntax as well. They are as follows:

Check Keyword Existence Anywhere:

Suppose, Wan to match both sides(SQL Syntax: “%keyword%”), that means,
If we have a keyword and wants all results that contains this keyword and have anything prior/next to it, then, we can use the following function:

UsersDataContext myDB = new MyDataContext();
List<Users> users= (List<Users>)myDB .TVUsers.Where(u => u.Username.Contains(keyword)).ToList();

This generates the SQL Query Like as follows:

SELECT * FROM Users WHERE Username Like [%keyword%]

Read All LinQ Tutorials By CodeSamplez.com

Check Keyword Existence At Beginning/End:

Similarly, for matching the beginning and endings are as follows:

//matching first part of the column
List<Users> users= (List<Users>)TVDB.Users.Where(u => u.Username.StartsWith(keyword)).ToList();

//matching last part of the column
List<Users> users= (List<Users>)TVDB.Users.Where(u => u.Username.EndsWith(keyword)).ToList();

If you like to use LinQ’s SQL like statement syntax, sure you can use that too. Here is the examples of alternative SQL Like syntax:

//checks existence of a keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.Contains(keyword)
                                   select u;
//check records that start with the keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.StartsWith(keyword)
                                   select u;

//check record that end with the keyword
List<Users> temp = (List<Users>)from u in TVDB.Users
                                   where u.Username.EndsWith(keyword)
                                   select u;

Using LinQ To SQL Like As SQL Syntax:

Also, there is another more useful alternative ways to achieve this, even more handy that can be used to search with a regular expression pattern. That is provided by a .NET class, ‘SqlMethods’ (under ‘System.Data.Linq.SqlClient’ namespace). Is has a method named ‘Like'(with 2 overloads) to which you can send parameters with keywords/patterns. Here is an example of using ‘SqlMethods.Like’ for achieve the same result as above:

List<Users> users = (List<Users>)from u in TVDB.Users
                                   where SqlMethods.Like(u.Username,"%"+keyword+"%")
                                   select u;

They will generate sql statements respectively as follows:

SELECT * FROM Users WHERE Username Like [%keyword%]

SELECT * FROM Users WHERE Username Like [keyword%]

SELECT * FROM Users WHERE Username Like [%keyword]

You can easily observe that ‘Contains(keyword)‘ method doing the work of ‘%keyword%’, ‘StartsWith(keyword)‘ doing the task of ‘%’ and ‘EndsWith(keyword)‘ doing the task of ‘%keyword’. On the other hand, with SQL Methods, you can use those symbols exactly like SQL query.

Hope this small LinQ to SQL like operator tutorial helps you understand the use of ‘like’ operator along with linq queries more easily and efficiently. I will continuously try to provide more advanced tutorial on LinQ to SQL as much as possible. If you have any question regarding this tutorial, feel free to ask them by commenting here. Happy coding 🙂