Using "Like" in LINQ to SQL |Linq to SQL Like Operator Or Linq to SQL 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. Thanks To LinQ To SQL, we can now get this functionality with help of few functions. They are as follows:

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:

Linq to SQL Like Operator

As a response for customer's question, I decided to write about using Like Operator in Linq to SQL queries.
Starting from a simple query from Northwind Database;

var query = from c in ctx.Customers

            where c.City == "London"

            select c;

The query that will be sent to the database will be:

SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City = [London]

There are some ways to write a Linq query that reaults in using Like Operator in the SQL statement:

1. Using String.StartsWith or String.Endswith

Writing the following query:

var query = from c in ctx.Customers

            where c.City.StartsWith("Lo")

            select c;

will generate this SQL statement:

SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [Lo%]

which is exactly what we wanted. Same goes with String.EndsWith.
But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name).
Using the query

var query = from c in ctx.Customers

            where c.City.StartsWith("L") && c.City.Contains("n")

            select c;

generates the statement:

SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L%]
AND      City LIKE [%n%]

which is not exactly what we wanted, and a little more complicated as well.

2. Using SqlMethods.Like method

Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods has a method called Like, that can be used in a Linq to SQL query:

var query = from c in ctx.Customers

            where SqlMethods.Like(c.City, "L_n%")

            select c;

This method gets the string expression to check (the customer's city in this example) and the patterns to test against which is provided in the same way you'd write a LIKE clause in SQL.

Using the above query generated the required SQL statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L_n%]


Hope this small linq tutorial helps you understand the use of 'like' operator along with a linq query easily. I will continuously try to provide more advanced tutorial on linq to sql as much as possible. Happy coding