Linq to SQL Like Operator

16 באוקטובר 2007

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.

Linq to SQL Like OperatorStarting 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
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%]


Add comment
facebook linkedin twitter email


  1. Zoli22 בפברואר 2008 ב 14:47

    Excellent! That's exactly what I needed.
    Thank you.

  2. WalangAlam29 במרץ 2008 ב 9:56

    thank you. it cured my big headache

  3. WalangAlam29 במרץ 2008 ב 9:58

    Thank you! it cured my big headache

  4. Mark Struzinski5 במאי 2008 ב 15:38

    Perfect! Exactly what I was looking for. Thanks!

  5. Aqueel Syed8 במאי 2008 ב 6:46

    Thanks, it was short and sweet.

  6. Jesse Donat29 במאי 2008 ב 3:53

    Not exactly what I needed, but lead me on the right path to get what I needed. Thank you!

  7. Dazed7513 ביוני 2008 ב 11:48

    thx, but for equals ignorecase there is a clean solution?

  8. Jason20 ביוני 2008 ב 4:20

    are you kidding me? this rocks! good find!

  9. Rob1 ביולי 2008 ב 23:21

    now I know, and knowing is half the battle.

  10. 4eburek2 ביולי 2008 ב 19:07

    Thanks a lot!
    One question: if I have an int column in sql table and make such LINQ query

    Dim res = From r In db.Table _
    Where r.IntColumn.ToString.Contains("25") _
    Select CStr(r.IntColumn) Distinct Take 20

    what SQL statement will be generated in this case?
    Can I see it somewhere?

  11. Travis Spencer14 ביולי 2008 ב 19:57

    Here's an example of using the method-oriented (or whatever it's called) syntax:

    var searchQuery = someData.Where(s => SqlMethods.Like(s.PhoneNumber, searchString));

  12. wilsont31 ביולי 2008 ב 6:34

    Thank you very much! your sqlmethod example saved my life…i have been stuck in a statement need field1+'%' like field2 and cannot figure how to do with linq.

  13. sam31 ביולי 2008 ב 10:26

    Hi, Any idea how to implement a Like for a SQL Text field in LINQ? Basically would like to use a patindex or charindex since its a Text field (not varchar). Thanks

  14. Jack3 באוקטובר 2008 ב 13:32

    Wow, it's so easy, thanks a lot, man.

  15. Neal23 באוקטובר 2008 ב 10:38


    Yea, if you use .Contains() then it's case sensative. I prefer to use RegEx to bypass that limitation:

    //Searches IList for items matching string
    public static IList Search(string searchString)
    return (from r in AppCache.Recipes
    where Regex.IsMatch(r.Title, searchString,
    orderby r.Title
    select r)

  16. Turgut17 בדצמבר 2008 ב 2:10

    Thank you…

  17. Cipper Tech19 בדצמבר 2008 ב 12:25

    Great, thanks a lot for the sample.

  18. Sri24 בדצמבר 2008 ב 13:21

    Thanks, really i needed it.

  19. Chris Charabaruk19 בפברואר 2009 ב 23:42

    Wish there was something like this for Entity Framework, too.

  20. HARTFORMER9 במרץ 2009 ב 18:31

    Thanks!!! Really useful! My Linq pain has been solved! :o)

  21. Sarvan23 במרץ 2009 ב 12:54

    Thank you very much… it clears my friends headache…

  22. David9 באפריל 2009 ב 21:00

    It does not work in EDM Query. The sentences are as following:
    string likeStr = searchText + "%";
    var queryUserID = from c in _commonContext.Accounts
    where SqlMethods.Like(c.UserID, likeStr)
    select c;
    int n = queryUserID.Count();

    The exception says: "LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method "

  23. Rakesh14 באפריל 2009 ב 7:36

    I am using LINQ to extract data from XML.But SqlMethods is not supported over client side.Can u suggest me something?

    I have to use wild card chars in LINQ on client side.

  24. mary17 באפריל 2009 ב 5:17

    from b in obj.user_DoctorSign
    from c in obj.sys_HospInfo
    from d in obj.sys_HospSection
    join a in obj.user_Doctor
    on new

    } equals new
    where c.HospName.Contains(HospName) && d.SectionName.Contains(SectionName) && b.ReallyName.Contains(DoctorName)
    select new
    when HosName and SectionName are null, DoctorName is null ,why query.Count()equals 0?

  25. Joe8 במאי 2009 ב 21:15

    In this case do you need to sanitize your search string, or will it get treated as a literal?

  26. Tony24 במאי 2009 ב 5:04

    Hello, I tryed to use this on Linq to Entities but it failed.

    LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)

    Could you try to help us with this?

  27. Daniel13 ביוני 2009 ב 16:35

    To see your queries use AnjLab Sql Profiler, it will help you optimize your queries.

  28. Pham16 ביוני 2009 ב 4:24

    Thanks. That's what i need.

  29. nick_trcari30 ביוני 2009 ב 12:04 roracgetri
    [url=]roracgetri[/url] zelbasvartrd acelleto
    rodronalzelp rictrel ouboeltald
    erbocdomac cacnar trtrtarosi
    pasaceldom tacnarelbon covartrvi
    trocacelcra pasvarge troctrocerd
    laacelvi booloo vardomt
    cnareltr daracel elrolre
    ouc4tliors taacrelrac viricv
    robocbasroo sitlacazel drondeldron
    dronricl liboacelsit rocaros
    vipasdronc4 trolomonac erlaelt
    cozellarelcn oloolov delorellar
    c4tmono ouricoup getdarol
    dronpaszelro rocozelmonli trnoelalgetb
    vargetta ricdeldarr eltbas
    troccag noacelacelli trocrelboc
    cacpasre botalig getacace
    lieroubocre c4tcta elrozel
    racgetvin eracchiolono cobocbocdarv
    ouracricdelb botrleto ercacel
    domlibaselts cnaliri rollapa
    cnarica boriclib libocpastr
    ordomcal lalitrdrond lichiorra
    oloccnabocze trocletoo booloeltal
    taerorro monbasr monzele
    licnasi boaltao varzelpasl
    rolrolrel baszele letozella
    oracboc reldardeltr zelelnoelb
    liracorb acelsitri getget
    getcorb cacotrc codarorcnado
    bositrel domboclaac ladronc4t
    acelno alzelb c4tdarric
    dronli letovardarc varpasb
    erlilic deldronre caalolo
    roletoelace roceltvarc4t riccocada
    bascaricl orpasro varc4tletota
    elacelbasle taelda racrelcvarle
    novibovilaz lic4tca dronc4tli
    reltachid latr

  30. movelove9 ביולי 2009 ב 15:23

    thank you.
    It can help me more.

  31. movelove9 ביולי 2009 ב 15:25

    It can help me more.
    thank you.

  32. Qasim Ali7 באוגוסט 2009 ב 11:22

    Thank you very much. I was wondering to know the use of like operator in LINQ. Your article give me insights.

  33. mano6 במאי 2010 ב 15:49

    Hi ,

    I am also getting the following error..!! can any one help me.

    LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression.

    Thanks in Advance,

  34. Shobhit Sheel25 ביולי 2011 ב 15:28

    Thanks Dude … Solved my problem

    Stay blessed

  35. Jeeva11 בינואר 2012 ב 11:33

    Its super … Really thanks to you

Comments are closed.