How to do SQL Like in Linq

Question

I have a procedure in SQL that I am trying to turn into Linq   SELECT O Id  O Name as Organization FROM Organizations O JOIN OrganizationsHierarchy OH ON O Id OH OrganizationsId where OH Hierarchy like    12      The line I am most concerned with is   where OH Hierarchy like    12      I have a column that stores the hierarchy like  1 3 12  for example so I just use   12   to search for it   My question is  what is the Linq or  NET equivalent to using the percent sign

User · Answer

I do always this   from h in OH where h Hierarchy Contains   12    select h   I know I don t use the like statement but it s work fine in the background is this translated into a query with a like statement

User · Answer

Contains is used in Linq  Just like Like is used in SQL    string  search   12              Where s   gt  s Hierarchy Contains  search     You can write your SQL script in Linq as Following     var result  Organizations Join OrganizationsHierarchy Where s  gt s Hierarchy Contains   12     s  gt s Id s  gt s OrganizationsId  org orgH   gt new  org orgH

User · Answer

System Data Linq SqlClient SqlMethods Like  mystring     string

User · Answer

Way late  but I threw this together to be able to do String comparisons using SQL Like style wildcards   public static class StringLikeExtensions            lt summary gt          Tests a string to be Like another string containing SQL Like style wildcards          lt  summary gt           lt param name  value  gt string to be searched lt  param gt           lt param name  searchString  gt the search string containing wildcards lt  param gt           lt returns gt value Like searchString  lt  returns gt           lt example gt value Like  a   lt  example gt           lt example gt value Like  a    lt  example gt           lt example gt value Like   b   lt  example gt           lt example gt value Like  a b   lt  example gt           lt example gt value Like  a b c   lt  example gt           lt remarks gt base author -- Ruard van Elburg from StackOverflow  modifications by dvn lt  remarks gt           lt remarks gt converted to a String extension by sja lt  remarks gt           lt seealso cref  https   stackoverflow com questions 1040380 wildcard-search-for-linq   gt      public static bool Like this String value  string searchString                bool result   false           var likeParts   searchString Split new char                      for  int i   0  i  lt  likeParts Length  i                          if  likeParts i     String Empty                                continue        a                              if  i    0                                if  likeParts Length    1      a                                        result   value Equals likeParts i   StringComparison OrdinalIgnoreCase                                     else     a   or  a b                                        result   value StartsWith likeParts i   StringComparison OrdinalIgnoreCase                                               else if  i    likeParts Length - 1      a b  or   b                                result  amp   value EndsWith likeParts i   StringComparison OrdinalIgnoreCase                             else     a b c                                int current   value IndexOf likeParts i   StringComparison OrdinalIgnoreCase                   int previous   value IndexOf likeParts i - 1   StringComparison OrdinalIgnoreCase                   result  amp   previous  lt  current                                   return result                  lt summary gt          Tests a string containing SQL Like style wildcards to be ReverseLike another string           lt  summary gt           lt param name  value  gt search string containing wildcards lt  param gt           lt param name  compareString  gt string to be compared lt  param gt           lt returns gt value ReverseLike compareString  lt  returns gt           lt example gt value ReverseLike  a   lt  example gt           lt example gt value ReverseLike  abc   lt  example gt           lt example gt value ReverseLike  ab   lt  example gt           lt example gt value ReverseLike  axb   lt  example gt           lt example gt value ReverseLike  axbyc   lt  example gt           lt remarks gt reversed logic of Like String extension lt  remarks gt      public static bool ReverseLike this String value  string compareString                bool result   false           var likeParts   value Split new char                    for  int i   0  i  lt  likeParts Length  i                          if  likeParts i     String Empty                                continue        a                              if  i    0                                if  likeParts Length    1      a                                        result   compareString Equals likeParts i   StringComparison OrdinalIgnoreCase                                     else     a   or  a b                                        result   compareString StartsWith likeParts i   StringComparison OrdinalIgnoreCase                                               else if  i    likeParts Length - 1      a b  or   b                                result  amp   compareString EndsWith likeParts i   StringComparison OrdinalIgnoreCase                             else     a b c                                int current   compareString IndexOf likeParts i   StringComparison OrdinalIgnoreCase                   int previous   compareString IndexOf likeParts i - 1   StringComparison OrdinalIgnoreCase                   result  amp   previous  lt  current                                   return result

User · Answer

Use this   from c in dc Organization where SqlMethods Like c Hierarchy     12     select

User · Answer

Use such code  try       using  DatosDataContext dtc   new DatosDataContext                  var query   from pe in dtc Personal Hgo                     where SqlMethods Like pe nombre        txtNombre Text                            select new                                               pe numero                                                   pe nombre                                dgvDatos DataSource   query ToList            catch  Exception ex        string mensaje   ex Message

User · Answer

In case you are not matching numeric strings  always good to have common case    Where oh   gt  oh Hierarchy ToUpper   Contains mySearchString ToUpper

User · Answer

Where oh   gt  oh Hierarchy Contains   12       You can also use  StartsWith   or  EndsWith

User · Answer

NET core now has EF Functions Like   var isMatch   EF Functions Like stringThatMightMatch  pattern

User · Answer

Try this  this works fine for me   from record in context Organization where record Hierarchy Contains 12  select record

User · Answer

Well indexOf works for me too  var result   from c in SampleList where c LongName IndexOf SearchQuery   gt   0 select c

User · Answer

I m assuming you re using Linq-to-SQL   see note below   If so  use string Contains  string StartsWith  and string EndsWith to generate SQL that use the SQL LIKE operator   from o in dc Organization join oh in dc OrganizationsHierarchy on o Id equals oh OrganizationsId where oh Hierarchy Contains    12    select new   o Id  o Name     or  from o in dc Organization where o OrganizationsHierarchy Hierarchy Contains    12    select new   o Id  o Name     Note       if you are using the ADO Net Entity Framework  EF   L2E  in  net 3 5  be aware that it will not do the same translation as Linq-to-SQL  Although L2S does a proper translation  L2E v1  3 5  will translate into a t-sql expression that will force a full table scan on the table you re querying unless there is another better discriminator in your where clause or join filters  Update  This is fixed in EF L2E v4   net 4 0   so it will generate a SQL LIKE just like L2S does

User · Answer

For those how tumble here like me looking for a way to a  SQL Like  method in LINQ  I ve something that is working very good   I m in a case where I cannot alter the Database in any way to change the column  collation  So I ve to find a way in my LINQ to do it   I m using the helper method SqlFunctions PatIndex witch act similarly to the real SQL LIKE  operator   First I need enumerate all possible diacritics  a word that I just learned  in the search value to get something like   d  j         gt  d         e        E j a      A        montreal   gt  montr         e        E  a      A       l montr  al   gt  montr         e        E  a      A       l   and then in LINQ for exemple    var city    montr         e        E  a      A       l   var data    from loc in  context Locations                      where SqlFunctions PatIndex city  loc City   gt  0                      select loc City  ToList       So for my needs I ve written a Helper Extension method      public static class SqlServerHelper                private static readonly List lt KeyValuePair lt string  string gt  gt  Diacritics   new List lt KeyValuePair lt string  string gt  gt                          new KeyValuePair lt string  string gt   A    a      A                      new KeyValuePair lt string  string gt   E            e        E                new KeyValuePair lt string  string gt   U    u      U                      new KeyValuePair lt string  string gt   C    c  C                  new KeyValuePair lt string  string gt   I    i    I                    new KeyValuePair lt string  string gt   O                            new KeyValuePair lt string  string gt   Y    Y      y                         public static string EnumarateDiacritics this string stringToDiatritics                        if  string IsNullOrEmpty stringToDiatritics Trim                     return stringToDiatritics               var diacriticChecked   string Empty               foreach  var c in stringToDiatritics ToCharArray                                  var diac   Diacritics FirstOrDefault o   gt  o Value ToCharArray   Contains c                    if  string IsNullOrEmpty diac Key                       continue                     Prevent from doing same letter Diacritic more than one time                 if  diacriticChecked Contains diac Key                       continue                   diacriticChecked    diac Key                   stringToDiatritics   stringToDiatritics Replace c ToString          diac Value                                    stringToDiatritics         stringToDiatritics                    return stringToDiatritics                    If any of you have suggestion to enhance this method  I ll be please to hear you

User · Answer

If you are using VB NET  then the answer would be      Here is what your where clause would look like     Where OH Hierarchy Like    12      Note      Matches zero or more characters  Here is the msdn article for the Like operator

[.net] How to do SQL Like % in Linq?

Examples related to .net

Examples related to linq

Examples related to tsql

Examples related to linq-to-entities

Examples related to sql-like