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 also provided for using the functionality of SQL operators/keywords. I will try to provide as many examples as possible on them from time to time. For today, My main concern is to make you familiar with the ‘Like’ keyword and construct an 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 a few readily 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 want all results that contain 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();
Code language: PHP (php)

This generates the SQL Query Like as follows:

SELECT * FROM Users WHERE Username Like [%keyword%]
Code language: CSS (css)

Read All LinQ Tutorials By CodeSamplez.com

Check Keyword Existence At the 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();Code language: JavaScript (javascript)

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;
Code language: PHP (php)

Using LinQ To SQL Like As SQL Syntax:

Also, there is another more useful alternative way 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). It 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’ to achieve the same result as above:

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

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]
Code language: CSS (css)

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

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