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