LIKE operator in LINQ

Question

Is there any way to compare strings in a C  LINQ expression similar to SQL s LIKE operator   Suppose I have a string list  On this list I want to search a string  In SQL  I could write   SELECT   FROM DischargePort WHERE PortName LIKE   BALTIMORE     Instead of the above   query want a linq syntax   using System Text RegularExpressions       var regex   new Regex sDischargePort  RegexOptions IgnoreCase   var sPortCode   Database DischargePorts                  Where p   gt  regex IsMatch p PortName                    Single   PortCode    My above LINQ syntax does not work  What have I got wrong

User · Answer

You can also use the EF function tested in  net5 public async Task lt IEnumerable lt District gt  gt  SearchDistrict string query  int stateId                        return await  dbContext                  Districts                  Include s   gt  s State                   Where s   gt  s StateId    stateId  amp  amp  EF Functions Like s Name   quot    query   quot                     AsNoTracking                    ToListAsync

User · Answer

adobrzyc had this great custom LIKE function - I just wanted to share the IEnumerable version of it  public static class LinqEx       private static readonly MethodInfo ContainsMethod   typeof string  GetMethod  quot Contains quot        private static readonly MethodInfo StartsWithMethod   typeof string  GetMethod  quot StartsWith quot   new     typeof string          private static readonly MethodInfo EndsWithMethod   typeof string  GetMethod  quot EndsWith quot   new     typeof string           private static Func lt TSource  bool gt  LikeExpression lt TSource  TMember gt  Expression lt Func lt TSource  TMember gt  gt  property  string value                var param   Expression Parameter typeof TSource    quot t quot            var propertyInfo   GetPropertyInfo property           var member   Expression Property param  propertyInfo Name            var startWith   value StartsWith  quot   quot            var endsWith   value EndsWith  quot   quot             if  startWith              value   value Remove 0  1            if  endsWith              value   value Remove value Length - 1  1            var constant   Expression Constant value           Expression exp           if  endsWith  amp  amp  startWith                        exp   Expression Call member  ContainsMethod  constant                     else if  startWith                        exp   Expression Call member  EndsWithMethod  constant                     else if  endsWith                        exp   Expression Call member  StartsWithMethod  constant                     else                       exp   Expression Equal member  constant                      return Expression Lambda lt Func lt TSource  bool gt  gt  exp  param  Compile               public static IEnumerable lt TSource gt  Like lt TSource  TMember gt  this IEnumerable lt TSource gt  source  Expression lt Func lt TSource  TMember gt  gt  parameter  string value                return source Where LikeExpression parameter  value                private static PropertyInfo GetPropertyInfo Expression expression                var lambda   expression as LambdaExpression          if  lambda    null              throw new ArgumentNullException  quot expression quot             MemberExpression memberExpr   null           switch  lambda Body NodeType                        case ExpressionType Convert                  memberExpr     UnaryExpression lambda Body  Operand as MemberExpression                  break              case ExpressionType MemberAccess                  memberExpr   lambda Body as MemberExpression                  break                     if  memberExpr    null              throw new InvalidOperationException  quot Specified expression is invalid  Unable to determine property info from expression  quot              var output   memberExpr Member as PropertyInfo           if  output    null              throw new InvalidOperationException  quot Specified expression is invalid  Unable to determine property info from expression  quot             return output

User · Answer

List lt Categories gt  categoriess          private void Buscar                         try                               categoriess   Contexto Categories ToList                    categoriess   categoriess Where n   gt  n CategoryID  gt   Convert ToInt32 txtCatID Text   amp  amp  n CategoryID  lt   Convert ToInt32 txtCatID1 Text   amp  amp   n CategoryName Contains txtCatName Text     ToList

User · Answer

Well    sometimes it may be uncomfortable to use Contains  StartsWith or EndsWith especially when searching value determine LIKE statment e g  passed  value   require from developer to use StartsWith function in expression  So I decided to write extension for IQueryable objects   Usage     numbers  11-000-00  00-111-00  00-000-11  var data1   parts Like p   gt  p Number    11        result  11-000-00  00-111-00  00-000-11  var data2   parts Like p   gt  p Number   11        result  11-000-00  var data3   parts Like p   gt  p Number    11       result  00-000-11   Code  public static class LinqEx       private static readonly MethodInfo ContainsMethod   typeof string  GetMethod  Contains        private static readonly MethodInfo StartsWithMethod   typeof string  GetMethod  StartsWith   new     typeof string          private static readonly MethodInfo EndsWithMethod   typeof string  GetMethod  EndsWith   new     typeof string           public static Expression lt Func lt TSource  bool gt  gt  LikeExpression lt TSource  TMember gt  Expression lt Func lt TSource  TMember gt  gt  property  string value                var param   Expression Parameter typeof TSource    t            var propertyInfo   GetPropertyInfo property           var member   Expression Property param  propertyInfo Name            var startWith   value StartsWith               var endsWith   value EndsWith                if  startWith              value   value Remove 0  1            if  endsWith              value   value Remove value Length - 1  1            var constant   Expression Constant value           Expression exp           if  endsWith  amp  amp  startWith                        exp   Expression Call member  ContainsMethod  constant                     else if  startWith                         exp   Expression Call member  EndsWithMethod  constant                     else if  endsWith                        exp   Expression Call member  StartsWithMethod  constant                     else                       exp   Expression Equal member  constant                      return Expression Lambda lt Func lt TSource  bool gt  gt  exp  param              public static IQueryable lt TSource gt  Like lt TSource  TMember gt  this IQueryable lt TSource gt  source  Expression lt Func lt TSource  TMember gt  gt  parameter  string value                return source Where LikeExpression parameter  value               private static PropertyInfo GetPropertyInfo Expression expression                var lambda   expression as LambdaExpression          if  lambda    null              throw new ArgumentNullException  expression             MemberExpression memberExpr   null           switch  lambda Body NodeType                        case ExpressionType Convert                  memberExpr     UnaryExpression lambda Body  Operand as MemberExpression                  break              case ExpressionType MemberAccess                  memberExpr   lambda Body as MemberExpression                  break                     if  memberExpr    null              throw new InvalidOperationException  Specified expression is invalid  Unable to determine property info from expression               var output   memberExpr Member as PropertyInfo           if  output    null              throw new InvalidOperationException  Specified expression is invalid  Unable to determine property info from expression              return output

User · Answer

As Jon Skeet and Marc Gravell already mentioned  you can simple take a contains condition  But in case of your like query  it s very dangerous to take a Single   statement  because that implies that you only find 1 result  In case of more results  you ll receive a nice exception     So I would prefer using FirstOrDefault   instead of Single     var first   Database DischargePorts FirstOrDefault p   gt  p PortName Contains  BALTIMORE     var portcode   first    null   first PortCode   string Empty

User · Answer

You can call the single method with a predicate   var portCode   Database DischargePorts                     Single p   gt  p PortName Contains  BALTIMORE                        PortCode

User · Answer

A simple as this  string   users   new string     Paul   Steve   Annick   Yannick        var result   from u in users where u Contains  nn   select u    Result -  Annick Yannick

User · Answer

In native LINQ you may use combination of Contains StartsWith EndsWith or RegExp   In LINQ2SQL use method SqlMethods Like        from i in db myTable     where SqlMethods Like i field   tra ata       select i   add Assembly  System Data Linq  in System Data Linq dll  to use this feature

User · Answer

Regex  no  But for that query you can just use     string filter    BALTIMORE     blah   Where row   gt  row PortName Contains filter    blah    If you really want SQL LIKE  you can use System Data Linq SqlClient SqlMethods Like       which LINQ-to-SQL maps to LIKE in SQL Server

User · Answer

Where e   gt  e Value StartsWith  BALTIMORE      This works like  LIKE  of SQL

User · Answer

Like Extension Linq   SQL LikeExtension Class Tested in  NET 5  public static class LikeExtension        private static string ColumnDataBase lt TEntity  TKey gt  IModel model  Expression lt Func lt TEntity  TKey gt  gt  predicate  where TEntity   class            ITable table   model              GetRelationalModel                Tables              First f   gt  f                  EntityTypeMappings                  First                    EntityType    model                  FindEntityType predicate                      Parameters                      First                    Type                          string column    predicate Body as MemberExpression  Member Name          string columnDataBase   table Columns First f   gt  f PropertyMappings Count f2   gt  f2 Property Name    column   gt  0  Name           return columnDataBase              public static IQueryable lt TEntity gt  Like lt TEntity  TKey gt  this DbContext context  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text  where TEntity   class            string columnDataBase   ColumnDataBase context Model  predicate           return context Set lt TEntity gt    FromSqlRaw context Set lt TEntity gt    ToQueryString      quot  WHERE   quot    columnDataBase    quot   LIKE  0  quot   text               public static async Task lt IEnumerable lt TEntity gt  gt  LikeAsync lt TEntity  TKey gt  this DbContext context  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text  CancellationToken cancellationToken  where TEntity   class            string columnDataBase   ColumnDataBase context Model  predicate           return await context Set lt TEntity gt    FromSqlRaw context Set lt TEntity gt    ToQueryString      quot  WHERE   quot    columnDataBase    quot   LIKE  0  quot   text  ToListAsync cancellationToken               public static async Task lt IEnumerable lt TEntity gt  gt  LikeAsync lt TEntity  TKey gt  this IQueryable lt TEntity gt  query  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text  CancellationToken cancellationToken  where TEntity   class            DbSet lt TEntity gt  entities   query as DbSet lt TEntity gt           string columnDataBase   ColumnDataBase entities EntityType Model  predicate           return await entities FromSqlRaw query ToQueryString      quot  WHERE   quot    columnDataBase    quot   LIKE  0  quot   text  ToListAsync cancellationToken               public static IQueryable lt TEntity gt  Like lt TEntity  TKey gt  this IQueryable lt TEntity gt  query  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text  where TEntity   class            DbSet lt TEntity gt  entities   query as DbSet lt TEntity gt           string columnDataBase   ColumnDataBase entities EntityType Model  predicate           return entities FromSqlRaw query ToQueryString      quot  WHERE   quot    columnDataBase    quot   LIKE  0  quot   text              Repository     public async Task lt IEnumerable lt TEntity gt  gt  LikeAsync lt TKey gt  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text  CancellationToken cancellationToken             return await context LikeAsync predicate  text  cancellationToken               public IQueryable lt TEntity gt  Like lt TKey gt  Expression lt Func lt TEntity  TKey gt  gt  predicate  string text             return context Like predicate  text           Use  IQueryable lt CountryEntity gt  result   countryRepository       Like k   gt  k Name   quot  Bra sz il  quot     Use Sync         Where w   gt  w DateRegister  lt  DateTime Now    Example         Take 10     Example    Or  IEnumerable lt CountryEntity gt  result   await countryRepository       LikeAsync k   gt  k Name   quot  Bra sz il  quot   cancellationToken     Use Async    Or  IQueryable lt CountryEntity gt  result   context Countries       Like k   gt  k Name   quot  Bra sz il  quot         Where w   gt  w Name    null     Example    Or  List lt CountryEntity gt  result2   await context Countries       Like k   gt  k Name   quot  Bra sz il  quot         Where w   gt  w Name    null    Example         ToListAsync      Use Async    Or  IEnumerable lt CountryEntity gt  result3   await context Countries       Where w   gt  w Name    null        LikeAsync k   gt  k Name   quot  Bra sz il  quot   cancellationToken     Use Async

User · Answer

Typically you use String StartsWith EndsWith Contains  For example   var portCode   Database DischargePorts                         Where p   gt  p PortName Contains  BALTIMORE                            Single                           PortCode    I don t know if there s a way of doing proper regular expressions via LINQ to SQL though   Note that it really does depend on which provider you re using - it would be fine in LINQ to Objects  it s a matter of whether the provider can convert the call into its native query format  e g  SQL    EDIT  As BitKFu says  Single should be used when you expect exactly one result - when it s an error for that not to be the case  Options of SingleOrDefault  FirstOrDefault or First should be used depending on exactly what s expected

User · Answer

Just add to string object extention methods    public static class StringEx       public static bool Contains this String str  string   Arr  StringComparison comp                if  Arr    null                        foreach  string s in Arr                                if  str IndexOf s  comp  gt  0                    return true                                     return false             public static bool Contains this String str string   Arr                if  Arr    null                        foreach  string s in Arr                                if  str Contains s                     return true                                     return false            usage    use namespase that contains this class   var sPortCode   Database DischargePorts              Where p   gt  p PortName Contains new string      BALTIMORE     StringComparison CurrentCultureIgnoreCase                 Single   PortCode

User · Answer

Ideally you should use StartWith or EndWith   Here is an example   DataContext  dc   new DCGeneral    List lt Person gt  lstPerson  dc GetTable lt Person gt    StartWith c  gt  c strNombre  ToList     return lstPerson

User · Answer

public static class StringEx               public static bool Contains this String str  string   Arr  StringComparison comp                        if  Arr    null                                foreach  string s in Arr                                        if  str IndexOf s  comp  gt  0                        return true                                                 return false                     public static bool Contains this String str string   Arr                        if  Arr    null                                foreach  string s in Arr                                        if  str Contains s                         return true                                                 return false                    var portCode   Database DischargePorts                     Single p   gt  p PortName Contains  new string     BALTIMORE     StringComparison CurrentCultureIgnoreCase                         PortCode

[c#] LIKE operator in LINQ

Examples related to c#

Examples related to linq

Examples related to sql-like