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
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!

הוסף תגובה
facebook linkedin twitter email

39 תגובות

  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

    @Dazed75

    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,
    RegexOptions.IgnoreCase)
    orderby r.Title
    select r)
    .ToList();
    }

  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
    {
    b.UserName,
    c.HospNumber,
    d.SectionNumber

    } equals new
    {
    a.UserName,
    a.HospNumber,
    a.SectionNumber
    }
    where c.HospName.Contains(HospName) && d.SectionName.Contains(SectionName) && b.ReallyName.Contains(DoctorName)
    select new
    {
    a.UserName,
    b.ReallyName,
    a.HospNumber,
    a.SectionNumber,
    c.HospName,
    d.SectionName
    };
    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

    http://lisitlidomt.com/riccnavarr.html roracgetri
    roracgetri
    [link=http://ornositlaer.com/lidronalal.html]roracgetri[/link]
    [url=http://rocarodron.com/pasdommo.html]roracgetri[/url]
    http://elteltlidronroc.com/libastrocl.htm zelbasvartrd acelleto
    rodronalzelp
    http://olocnadomletoro.com/olotrzelri.htm rictrel ouboeltald
    erbocdomac
    http://clizelallinodro.com/trlidro.htm cacnar trtrtarosi
    pasaceldom
    http://noacelsitaclibo.com/c4tdom.htm tacnarelbon covartrvi
    trocacelcra
    http://erlilacroldompa.com/chiboctrocr.htm pasvarge troctrocerd
    laacelvi
    http://alliactrsitracr.com/trocrolsitz.htm booloo vardomt
    cnareltr
    http://domvarboalelelt.com/boctadeler.htm daracel elrolre
    ouc4tliors
    http://roreltarositdel.com/accdronalze.htm taacrelrac viricv
    robocbasroo
    http://pasoutrcaletoli.com/ccnaalge.htm sitlacazel drondeldron
    dronricl
    http://pasacelacdronc4.com/letoroc.htm liboacelsit rocaros
    vipasdronc4
    http://dronlitrdrondro.com/rolaceltrcl.htm trolomonac erlaelt
    cozellarelcn
    http://letoelrorobocob.com/darvize.htm oloolov delorellar
    c4tmono
    http://cnaacborovarpas.com/cotatrocnod.htm ouricoup getdarol
    dronpaszelro
    http://lisitboctasitre.com/monacdomr.htm rocozelmonli trnoelalgetb
    vargetta
    http://letocoboeltdarl.com/tapastrcolil.htm ricdeldarr eltbas
    troccag
    http://roricchirolnoer.com/tadron.htm noacelacelli trocrelboc
    cacpasre
    http://elboctrviacchit.com/pasololirol.htm botalig getacace
    lieroubocre
    http://acaclapasracoug.com/botrel.htm c4tcta elrozel
    racgetvin
    http://lirelletoalvart.com/moncab.htm eracchiolono cobocbocdarv
    ouracricdelb
    http://allisitlivilile.com/elnoletol.htm botrleto ercacel
    domlibaselts
    http://varbocsitroltro.com/olocnabonoou.htm cnaliri rollapa
    cnarica
    http://zelzeltroccolat.com/troccnasit.htm boriclib libocpastr
    ordomcal
    http://racmonvielcalav.com/monchicodo.htm lalitrdrond lichiorra
    oloccnabocze
    http://liacboczeldomre.com/aloralorm.htm trocletoo booloeltal
    taerorro
    http://darerzelmoncoc4.com/aceltro.htm monbasr monzele
    licnasi
    http://acerdrondronric.com/noerladronc.htm boaltao varzelpasl
    rolrolrel
    http://erviracricacchi.com/orletorolc.htm baszele letozella
    oracboc
    http://racdelouroltaou.com/rovicnalet.htm reldardeltr zelelnoelb
    liracorb
    http://acdronacelbocou.com/sitvis.htm acelsitri getget
    getcorb
    http://vioracrovarrell.com/racc4tcre.htm cacotrc codarorcnado
    bositrel
    http://cnaorgetlielace.com/trocrollet.htm domboclaac ladronc4t
    acelno
    http://trocolobomonolo.com/acelalacelt.htm alzelb c4tdarric
    dronli
    http://ernoliletodomno.com/trlibassite.htm letovardarc varpasb
    erlilic
    http://acacsitrolbocsi.com/dronroln.htm deldronre caalolo
    roletoelace
    http://bocvarbasgetlab.com/delzelc.htm roceltvarc4t riccocada
    bascaricl
    http://ouricviracdardo.com/rictamo.htm orpasro varc4tletota
    elacelbasle
    http://liletolimongetd.com/oucagete.htm taelda racrelcvarle
    novibovilaz
    http://sitricdroncnaco.com/zellaboc.htm lic4tca dronc4tli
    reltachid
    http://cacnanoliacorre.com/eltzelde.htm latr
    troclaalcr

  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,
    mano

  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.