How to operator in Entity Framework?

Question

We are trying to implement the "LIKE" operator in the Entity Framework for our objects with string fields, but it does not seem to be supported. Has anyone else tried to do something like this?

This post reports the issue we have. We could use contains, but this only matches the most trivial case of LIKE. The combination contains startswith, endswith, and indexof gets us there, but requires a translation between standard wildcards and Linq to Entities code.

Answer 1

This is an old post now, but for those looking for an answer, this link should help.

Shortened version:

SqlFunctions.PatIndex method - returns the initial position of the first occurrence of the pattern in the specified expression or zeros if the pattern is not found for all valid types of text and character data

Namespace: System.Data.Objects.SqlClient Assembly: System.Data.Entity (in System.Data.Entity.dll)

A little explanation also appears in this forum thread .

Answer 2

I don't know anything about EF, but in LINQ to SQL, you usually express a LIKE clause with String.Contains:

where entity.Name.Contains("xyz") 

translates to

 WHERE Name LIKE '%xyz%' 

(Use StartsWith and EndsWith for other behavior.)

I'm not quite sure if this is useful because I don’t understand what you mean when you say that you are trying to implement LIKE. If I misunderstood, let me know and I will delete this answer :)

Answer 3

I had the same problem.

I have currently set up client-side wildcard filtering based on http://www.codeproject.com/Articles/11556/Converting-Wildcards-to-Regexes?msg=1423024#xx1423024xx - it is simple and works as expected.

I found another discussion on this topic: http://forums.asp.net/t/1654093.aspx/2/10
This post looks promising if you are using Entity Framework> = 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 => SqlFunctions.PatIndex("%CD%BLUE%", x.ProductName) > 0); 

Note. This solution is for SQL Server only, as it uses the non-standard PATINDEX function.

Answer 4

Update: in EF 6.2 there is a similar operator

 Where(i => DbFunctions.Like(searchstring ,like expression) 

Answer 5

Entity Framework Core 2.0 added LIKE statement:

 var query = from e in _context.Employees where EF.Functions.Like(e.Title, "%developer%") select e; 

Compared to ... where e.Title.Contains("developer") ... it really translates to SQL LIKE , not the CHARINDEX that we see for the Contains method.

Answer 6

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

Answer 7

If you use MS Sql, I wrote 2 extension methods to support the% character for pattern matching. (Requires LinqKit)

 public static class ExpressionExtension { public static Expression<Func<T, bool>> Like<T>(Expression<Func<T, string>> expr, string likeValue) { var paramExpr = expr.Parameters.First(); var memExpr = expr.Body; if (likeValue == null || likeValue.Contains('%') != true) { Expression<Func<string>> valExpr = () => likeValue; var eqExpr = Expression.Equal(memExpr, valExpr.Body); return Expression.Lambda<Func<T, bool>>(eqExpr, paramExpr); } if (likeValue.Replace("%", string.Empty).Length == 0) { return PredicateBuilder.True<T>(); } likeValue = Regex.Replace(likeValue, "%+", "%"); if (likeValue.Length > 2 && likeValue.Substring(1, likeValue.Length - 2).Contains('%')) { likeValue = likeValue.Replace("[", "[[]").Replace("_", "[_]"); Expression<Func<string>> valExpr = () => 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<Func<T, bool>>(neExpr, paramExpr); } if (likeValue.StartsWith("%")) { if (likeValue.EndsWith("%") == true) { likeValue = likeValue.Substring(1, likeValue.Length - 2); Expression<Func<string>> valExpr = () => likeValue; var containsExpr = Expression.Call(memExpr, typeof(String).GetMethod("Contains", new[] { typeof(string) }), valExpr.Body); return Expression.Lambda<Func<T, bool>>(containsExpr, paramExpr); } else { likeValue = likeValue.Substring(1); Expression<Func<string>> valExpr = () => likeValue; var endsExpr = Expression.Call(memExpr, typeof(String).GetMethod("EndsWith", new[] { typeof(string) }), valExpr.Body); return Expression.Lambda<Func<T, bool>>(endsExpr, paramExpr); } } else { likeValue = likeValue.Remove(likeValue.Length - 1); Expression<Func<string>> valExpr = () => likeValue; var startsExpr = Expression.Call(memExpr, typeof(String).GetMethod("StartsWith", new[] { typeof(string) }), valExpr.Body); return Expression.Lambda<Func<T, bool>>(startsExpr, paramExpr); } } public static Expression<Func<T, bool>> AndLike<T>(this Expression<Func<T, bool>> predicate, Expression<Func<T, string>> expr, string likeValue) { var andPredicate = Like(expr, likeValue); if (andPredicate != null) { predicate = predicate.And(andPredicate.Expand()); } return predicate; } public static Expression<Func<T, bool>> OrLike<T>(this Expression<Func<T, bool>> predicate, Expression<Func<T, string>> expr, string likeValue) { var orPredicate = Like(expr, likeValue); if (orPredicate != null) { predicate = predicate.Or(orPredicate.Expand()); } return predicate; } } 

Using

 var orPredicate = PredicateBuilder.False<People>(); orPredicate = orPredicate.OrLike(per => per.Name, "He%llo%"); orPredicate = orPredicate.OrLike(per => per.Name, "%Hi%"); var predicate = PredicateBuilder.True<People>(); predicate = predicate.And(orPredicate.Expand()); predicate = predicate.AndLike(per => per.Status, "%Active"); var list = dbContext.Set<People>().Where(predicate.Expand()).ToList(); 

in ef6 and it should translate to

 .... from People per where ( patindex(@p__linq__0, per.Name) <> 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 '

Answer 8

re: "we would like to be able to match blah blah foobar foo? bar? foo * bar? and other complex patterns." I have not actually tried this (not needed yet), but have you tried using System.Text.RegularExpressions.RegEx?

Answer 9

You can use the real one, as in Link to Entities, quite easily

Add

  <Function Name="String_Like" ReturnType="Edm.Boolean"> <Parameter Name="searchingIn" Type="Edm.String" /> <Parameter Name="lookingFor" Type="Edm.String" /> <DefiningExpression> searchingIn LIKE lookingFor </DefiningExpression> </Function> 

for your EDMX in this tag:

EDMX: EDMX / EDMX: Duration / EDMX: ConceptualModels / Scheme

Also remember the namespace in the <schema namespace="" /> attribute

Then add the extension class to the specified 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 be displayed in the EDMX function.

Further information here: http://jendaperl.blogspot.be/2011/02/like-in-linq-to-entities.html