none
How to generate the SQL 'like' LinQ in LS Query

    Question

  • Hello,

    It a very common scenario: I want to get the fuzzy query result like what I usually in SQL SP.

    But I found LS doesn't support SqlMethods.Like and  Regex. And the "Contains" doesn't work well in my case.

    string query_parameter = "%gg%uu%"

    Select * from tab_A

    Where Col_A like query_parameter 

    query = query.Where(w => w.Contacts.Any(c => c.Telephones.Any(p =>   p.PhoneNumber.Contains(PhoneNumber)  )));  

    Thanks,

    Bob



    • Edited by Bob Zhao Friday, July 06, 2012 9:02 PM
    Friday, July 06, 2012 9:00 PM

Answers

All replies

  • You don't need to put % characters in a Contains method.  You would just say:

    p.PhoneNumber.Contains("701").  That will give you all phone numbers with the string "701" in them.


    http://blogs.msdn.com/b/eric_erhardt/

    Friday, July 06, 2012 9:19 PM
  • It only work for simple query, but doesn't support fuzzy search.

    Exist PhoneNumbers:  "206-555-7777" and "206 555 7777", how to get both of them out?

    SQL query is : 

    Where PhoneNumber like "%206%555%7777%"


    • Edited by Bob Zhao Friday, July 06, 2012 9:30 PM
    Friday, July 06, 2012 9:29 PM
  • See this discussion:

    http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework

    The relevant part is towards the bottom:

    Use SqlFunctions.PatIndex:

    http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.patindex.aspx

    Like this:

    var q = EFContext.Products.Where(x => 
    SqlFunctions.PatIndex("%CD%BLUE%", x.ProductName) > 0); 
    

    Note: this solution is for SQL-Server only, because it uses non-standard PATINDEX function.

    I haven't tried it, but it looks promising.  You would say query = query.Where(w => w.Contacts.Any(c => c.Telephones.Any(p =>   SqlFunctions.PatIndex("%206%555%7777%") > 0  ))); 


    http://blogs.msdn.com/b/eric_erhardt/

    Friday, July 06, 2012 9:41 PM
  • Hi Eric,

    Thanks for your help. Finally, it works.

    I have to add the full reference path to SqlFunctions , like :  System.Data.Objects.SqlClient.SqlFunctions. Otherwise, the syntax check will get error, even I have added "using System.Data.SqlClient;"

    Here is the further question: Why the native System.Data.Linq.SqlClient.SqlMethods.Like() gets the run time error, while the new  added  System.Data.Objects.SqlClient.SqlFunctions works well?

    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.

    Is this caused by the .net framework ?

    Thanks,

    Bob


    • Edited by Bob Zhao Friday, July 06, 2012 10:59 PM
    Friday, July 06, 2012 10:53 PM
  • LINQ to Entities obviously supports System.Data.Objects.SqlClient.SqlFunctions, but not System.Data.Linq.SqlClient.SqlMethods.Like.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Saturday, July 07, 2012 2:04 AM