Like Operator in Entity Framework

Question

We re trying to implement the  LIKE  operator in Entity Framework for our entities with string fields  but it doesn t appear to be supported   Has anyone else tried to do something like this   This blog post summarizes the issue we re having   We could use contains  but that only matches the most trivial case for LIKE   Combining contains  startswith  endswith  and indexof gets us there  but requires a translation between standard wildcards and Linq to Entities code

User · Answer

I had the same problem  For now  I ve settled with client-side Wildcard Regex filtering based on http   www codeproject com Articles 11556 Converting-Wildcards-to-Regexes msg 1423024 xx1423024xx - it s simple and works as expected  I ve found another discussion on this topic  http   forums asp net t 1654093 aspx 2 10 This post looks promising if you use Entity Framework  gt   4 0   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   gt  SqlFunctions PatIndex  quot  CD BLUE  quot   x ProductName   gt  0     Note  this solution is for SQL-Server only  because it uses non-standard PATINDEX function

User · Answer

There is LIKE operator is added in Entity Framework Core 2 0   var query   from e in  context Employees                     where EF Functions Like e Title    developer                        select e    Comparing to     where e Title Contains  developer       it is really translated to SQL LIKE rather than CHARINDEX we see for Contains method

User · Answer

You can use a real like in Link to Entities quite easily  Add       lt Function Name  String Like  ReturnType  Edm Boolean  gt         lt Parameter Name  searchingIn  Type  Edm String    gt         lt Parameter Name  lookingFor  Type  Edm String    gt         lt DefiningExpression gt          searchingIn LIKE lookingFor        lt  DefiningExpression gt       lt  Function gt    to your EDMX in this tag       edmx Edmx edmx Runtime edmx ConceptualModels Schema   Also remember the namespace in the  lt schema namespace      gt  attribute  Then add an extension class in the above namespace   public static class Extensions        EdmFunction  DocTrails3 Net Database Models    String Like        public static Boolean Like this String searchingIn  String lookingFor                throw new Exception  Not implemented              This extension method will now map to the EDMX function   More info here  http   jendaperl blogspot be 2011 02 like-in-linq-to-entities html

User · Answer

Update  In EF 6 2 there is a like operator Where obj   gt  DbFunctions Like obj Column    quot  expression  quot

User · Answer

This is an old post now  but for anyone looking for the answer  this link should help  Go to this answer if you are already using EF 6 2 x  To this answer if you re using EF Core 2 x  Short version   SqlFunctions PatIndex method - returns the starting position of the first occurrence of a pattern in a specified expression  or zeros if the pattern is not found  on all valid text and character data types  Namespace   System Data Objects SqlClient Assembly   System Data Entity  in System Data Entity dll   A bit of an explanation also appears in this forum thread

User · Answer

For EfCore here is a sample to build LIKE expression  protected override Expression lt Func lt YourEntiry  bool gt  gt  BuildLikeExpression string searchText                var likeSearch       searchText              return t   gt  EF Functions Like t Code  likeSearch                         EF Functions Like t FirstName  likeSearch                         EF Functions Like t LastName  likeSearch            Calling method  var query   dbContext Set lt YourEntity gt    Where BuildLikeExpression  Text

User · Answer

if you re using MS Sql  I have wrote 2 extension methods to support the   character for wildcard search   LinqKit is required   public static class ExpressionExtension       public static Expression lt Func lt T  bool gt  gt  Like lt T gt  Expression lt Func lt T  string gt  gt  expr  string likeValue                var paramExpr   expr Parameters First            var memExpr   expr Body           if  likeValue    null    likeValue Contains         true                        Expression lt Func lt string gt  gt  valExpr        gt  likeValue              var eqExpr   Expression Equal memExpr  valExpr Body               return Expression Lambda lt Func lt T  bool gt  gt  eqExpr  paramExpr                      if  likeValue Replace      string Empty  Length    0                        return PredicateBuilder True lt T gt                        likeValue   Regex Replace likeValue                       if  likeValue Length  gt  2  amp  amp  likeValue Substring 1  likeValue Length - 2  Contains                             likeValue   likeValue Replace             Replace                          Expression lt Func lt string gt  gt  valExpr        gt  likeValue              var patExpr   Expression Call typeof SqlFunctions  GetMethod  PatIndex                   new     typeof string   typeof string      valExpr Body  memExpr               var neExpr   Expression NotEqual patExpr  Expression Convert Expression Constant 0   typeof int                  return Expression Lambda lt Func lt T  bool gt  gt  neExpr  paramExpr                      if  likeValue StartsWith                             if  likeValue EndsWith         true                                likeValue   likeValue Substring 1  likeValue Length - 2                   Expression lt Func lt string gt  gt  valExpr        gt  likeValue                  var containsExpr   Expression Call memExpr  typeof String  GetMethod  Contains                       new     typeof string      valExpr Body                   return Expression Lambda lt Func lt T  bool gt  gt  containsExpr  paramExpr                             else                               likeValue   likeValue Substring 1                   Expression lt Func lt string gt  gt  valExpr        gt  likeValue                  var endsExpr   Expression Call memExpr  typeof String  GetMethod  EndsWith                       new     typeof string      valExpr Body                   return Expression Lambda lt Func lt T  bool gt  gt  endsExpr  paramExpr                                   else                       likeValue   likeValue Remove likeValue Length - 1               Expression lt Func lt string gt  gt  valExpr        gt  likeValue              var startsExpr   Expression Call memExpr  typeof String  GetMethod  StartsWith                   new     typeof string      valExpr Body               return Expression Lambda lt Func lt T  bool gt  gt  startsExpr  paramExpr                        public static Expression lt Func lt T  bool gt  gt  AndLike lt T gt  this Expression lt Func lt T  bool gt  gt  predicate  Expression lt Func lt T  string gt  gt  expr  string likeValue                var andPredicate   Like expr  likeValue           if  andPredicate    null                        predicate   predicate And andPredicate Expand                       return predicate             public static Expression lt Func lt T  bool gt  gt  OrLike lt T gt  this Expression lt Func lt T  bool gt  gt  predicate  Expression lt Func lt T  string gt  gt  expr  string likeValue                var orPredicate   Like expr  likeValue           if  orPredicate    null                        predicate   predicate Or orPredicate Expand                       return predicate            usage  var orPredicate   PredicateBuilder False lt People gt     orPredicate   orPredicate OrLike per   gt  per Name   He llo     orPredicate   orPredicate OrLike per   gt  per Name    Hi      var predicate   PredicateBuilder True lt People gt     predicate   predicate And orPredicate Expand     predicate   predicate AndLike per   gt  per Status    Active     var list   dbContext Set lt People gt    Where predicate Expand    ToList          in ef6 and it  should translate to        from People per where       patindex  p  linq  0  per Name   lt  gt  0     or per Name like  p  linq  1 escape       and per Status like  p  linq  2 escape           p  linq  0     He llo     p  linq  1     Hi     p  linq 2     Active

User · Answer

It is specifically mentioned in the documentation as part of Entity SQL   Are you getting an error message      LIKE and ESCAPE    If an AdventureWorksEntities Product contained a Name     with the value  Down Tube   the following query would find that     value  Select value P Name FROM AdventureWorksEntities Product      as P where P Name LIKE  DownA    ESCAPE  A      LIKE Select value P Name FROM AdventureWorksEntities Product      as P where P Name like  BB     http   msdn microsoft com en-us library bb399359 aspx

User · Answer

I don t know anything about EF really  but in LINQ to SQL you usually express a LIKE clause using String Contains   where entity Name Contains  xyz     translates to  WHERE Name LIKE   xyz      Use StartsWith and EndsWith for other behaviour    I m not entirely sure whether that s helpful  because I don t understand what you mean when you say you re trying to implement LIKE  If I ve misunderstood completely  let me know and I ll delete this answer

[.net] Like Operator in Entity Framework?

Examples related to .net

Examples related to sql-server

Examples related to entity-framework

Examples related to linq-to-entities