.net 像实体框架中的运算符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1033007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Like Operator in Entity Framework?
提问by brien
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?
我们正在尝试在实体框架中为具有字符串字段的实体实现“LIKE”运算符，但它似乎不受支持。有没有其他人试图做这样的事情？
This blog postsummarizes 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.
这篇博文总结了我们遇到的问题。我们可以使用 contains，但它只匹配 LIKE 的最微不足道的情况。组合 contains、startswith、endswith 和 indexof 使我们到达那里，但需要在标准通配符和 Linq to Entities 代码之间进行转换。
采纳答案by Yann Duran
This is an old post now, but for anyone looking for the answer, this linkshould help. Go to this answerif you are already using EF 6.2.x. To this answerif you're using EF Core 2.x
这是一个旧帖子，但对于任何寻找答案的人来说，这个链接应该会有所帮助。如果您已经在使用 EF 6.2.x，请转到此答案。为了这个答案，如果你正在使用EF核心2.X
Short version:
精简版：
SqlFunctions.PatIndexmethod - 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
SqlFunctions.PatIndex方法 - 在所有有效的文本和字符数据类型上返回指定表达式中模式第一次出现的起始位置，如果找不到模式则返回零
Namespace: System.Data.Objects.SqlClient Assembly: System.Data.Entity (in System.Data.Entity.dll)
命名空间：System.Data.Objects.SqlClient 程序集：System.Data.Entity（在 System.Data.Entity.dll 中）
A bit of an explanation also appears in this forum thread.
这个论坛帖子中也出现了一些解释。
回答by Jon Skeet
I don't know anything about EF really, but in LINQ to SQL you usually express a LIKE clause using String.Contains:
我对 EF 一无所知，但在 LINQ to SQL 中，您通常使用 String.Contains 表示 LIKE 子句：
where entity.Name.Contains("xyz")
translates to
翻译成
WHERE Name LIKE '%xyz%'
(Use StartsWithand EndsWithfor other behaviour.)
（使用StartsWith和EndsWith用于其他行为。）
I'm not entirely sure whether that's helpful, because I don't understand what you mean when you say you're trying to implementLIKE. If I've misunderstood completely, let me know and I'll delete this answer :)
我不完全确定这是否有帮助，因为当你说你试图实现LIKE时，我不明白你的意思。如果我完全误解了，请告诉我，我会删除此答案:)
回答by surfen
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.
现在，我已经解决了基于http://www.codeproject.com/Articles/11556/Converting-Wildcards-to-Regexes?msg=1423024#xx1423024xx 的客户端通配符/正则表达式过滤- 它很简单并且可以作为预期的。
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 >= 4.0:
我发现了关于这个主题的另一个讨论：http: //forums.asp.net/t/1654093.aspx/2/10
如果您使用实体框架 >= 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);
使用 SqlFunctions.PatIndex：
http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.patindex.aspx
像这样：
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.
注意：此解决方案仅适用于 SQL-Server，因为它使用了非标准的 PATINDEX 函数。
回答by Dmitry Pavlov
There is LIKEoperator is added in Entity Framework Core 2.0:
在 中LIKE添加了运算符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 SQLLIKErather than CHARINDEXwe see for Containsmethod.
与... where e.Title.Contains("developer") ...它相比实际上是翻译为SQLLIKE而不是CHARINDEX我们看到的Contains方法。
回答by Lode Vlaeminck
Update: In EF 6.2 there is a like operator
更新：在 EF 6.2 中有一个 like 运算符
Where(i => DbFunctions.Like(searchstring ,like expression)
回答by Robert Harvey
It is specifically mentioned in the documentation as part of Entity SQL. Are you getting an error message?
它在文档中作为 Entity SQL 的一部分被特别提及。您是否收到错误消息？
// 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%'
回答by Duy Hoang
For EfCore here is a sample to build LIKE expression
对于 EfCore，这里是构建 LIKE 表达式的示例
protected override Expression<Func<YourEntiry, bool>> BuildLikeExpression(string searchText)
{
var likeSearch = $"%{searchText}%";
return t => EF.Functions.Like(t.Code, likeSearch)
|| EF.Functions.Like(t.FirstName, likeSearch)
|| EF.Functions.Like(t.LastName, likeSearch);
}
//Calling method
var query = dbContext.Set<YourEntity>().Where(BuildLikeExpression("Text"));
回答by Steven Chong
if you're using MS Sql, I have wrote 2 extension methods to support the % character for wildcard search. (LinqKit is required)
如果您使用的是 MS Sql，我已经编写了 2 个扩展方法来支持通配符搜索的 % 字符。（需要 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;
}
}
usage
用法
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
在 ef6 中，它应该转换为
....
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'
', @p__linq__0 = '%He%llo%', @p__linq__1 = '%Hi%', @p__linq_2 = '%Active'
回答by brechtvhb
You can use a real like in Link to Entities quite easily
你可以很容易地在 Link to Entities 中使用 real like
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>
to your EDMX in this tag:
到此标签中的 EDMX：
edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/Schema
edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/Schema
Also remember the namespace in the <schema namespace="" />attribute
还要记住<schema namespace="" />属性中的命名空间
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.
此扩展方法现在将映射到 EDMX 函数。
More info here: http://jendaperl.blogspot.be/2011/02/like-in-linq-to-entities.html
更多信息：http: //jendaperl.blogspot.be/2011/02/like-in-linq-to-entities.html
回答by th2tran
re: "we'd like to be able to match on blah blah foobar foo?bar ?foo*bar? and other complex patterns." I haven't actually tried this (haven't needed to yet), but have you tried using System.Text.RegularExpressions.RegEx?
回复：“我们希望能够匹配 blah blah foobar foo?bar ?foo*bar? 和其他复杂模式。” 我实际上还没有尝试过（还不需要），但是您是否尝试过使用 System.Text.RegularExpressions.RegEx？
