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 you need to maintain consistency between data relations. Simple data retrieval, insertion, deletion, update, etc., can be done in a straightforward way, in some cases just by calling some functions on the LinQ objects. Several ways are 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 widely used to perform search operations on database tables. Thanks to LinQ and SQL, we can now get this functionality with the help of a few readily given functions or even query syntax. 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)

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)

You can use LinQ’s SQL-like statement syntax, too. Here are 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 two 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 the ‘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 as many advanced tutorials on LinQ to SQL as possible. If you have any questions regarding this tutorial, feel free to ask them by commenting here. Happy coding 🙂