/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Monday, September 30, 2013

Linq to SQL Like Operator Using String.StartsWith or String.Endswith


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%]
Enjoy!
Read More »

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago