How to generate the SQL 'like' LinQ in LS Query RRS feed

  • 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)  )));  



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


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.

    Friday, July 6, 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 6, 2012 9:30 PM
    Friday, July 6, 2012 9:29 PM
  • See this discussion:

    The relevant part is towards the bottom:

    Use SqlFunctions.PatIndex:

    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  )));

    Friday, July 6, 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 ?



    • Edited by Bob Zhao Friday, July 6, 2012 10:59 PM
    Friday, July 6, 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 7, 2012 2:04 AM