如何让 LINQ 执行(SQL)LIKE 范围搜索 [英] How to make LINQ execute a (SQL) LIKE range search
问题描述
我非常需要帮助,我已经尝试这样做了一段时间了.
I am in big need of help, i have been trying to do this for some time now.
所以我有这个查询:
Select name from BlaBlaBla
order by
case when name like '9%' then 1 end,
case when name like '8%' then 1 end,
case when name like '7%' then 1 end,
case when name like '6%' then 1 end,
case when name like '5%' then 1 end,
case when name like '4%' then 1 end,
case when name like '3%' then 1 end,
case when name like '2%' then 1 end,
case when name like '1%' then 1 end,
case when name like '0%' then 1 end,
name
我想在一个新的 C#、Asp.Net 类中实现它,在我的解决方案中,到域项目,所以它将是一个 OrderType 过滤器,对于某些功能......
And I want to implement it in a new C#, Asp.Net, class, in my Solution, to the Domain Project, so it will be an OrderType Filter, for some function...
现在我有这个:
var param = Expression.Parameter(typeof(T), "item");
var paramName = Expression.Property(param, "Name");
var regexMatch = Expression.Constant("^[0-9]");
var startsWithDigit = Expression.Call(typeof(Regex), "IsMatch",
null, paramName);
var lambda = Expression.Lambda<Func<T, bool>>(startsWithDigit,
param);
return namesList.OrderBy(lambda)
.ThenBy(BlaBla1())
.ThenByDescending(BlaBla2())
.ThenByDescending(BlaBla3())
.ThenBy(BlaBla4());
但它告诉我,Expression 不包含IsMatch"方法.
But it tells me, that Expression does not contain "IsMatch" method.
你能帮我吗?谢谢!!!
Can you please help me? Thank you!!!
推荐答案
这里的问题是包含 Regex
的表达式无法转换为 SQL,因此即使您成功构建了一个正确的表达式,您不能在 LINQ 到 SQL 后端使用它.但是,SQL 的 LIKE
方法也支持像 [0-9]
这样的范围通配符,所以诀窍是让你的 LINQ 转换为包含 LIKE
的 SQL> 声明.
The problem here is that expressions containing Regex
can't be translated to SQL, so even when you'd succeed in building a correct expression, you can't use it in LINQ to a SQL backend. However, SQL's LIKE
method also supports range wildcards like [0-9]
, so the trick is to make your LINQ translate to SQL containing a LIKE
statement.
LINQ-to-SQL 提供了使用 SQL LIKE
语句显式的可能性:
LINQ-to-SQL offers the possibility to use the SQL LIKE
statement explicitly:
return namesList.OrderBy(r => SqlMethods.Like(r.Name, "[0-9]%")) ...
这个SqlMethods 类只能在 LINQ-to-SQL 中使用.在实体框架中,有一些字符串函数可以转换为 LIKE
implicitly,但它们都没有启用范围通配符 ([x-y]
).在 EF 中,像 ...
This SqlMethods class can only be used in LINQ-to-SQL though. In Entity Framework there are string functions that translate to LIKE
implicitly, but none of them enable the range wildcard ([x-y]
). In EF a statement like ...
return namesList.OrderBy(r => r.Name.StartsWith("[0-9]")) ...
...会变成废话:
[Name] LIKE '~[0-9]%' ESCAPE '~'
即它徒劳地寻找以文字字符串[0-9]"开头的名称.所以只要你继续使用 LINQ-to-SQL SqlMethods.Like
就是要走的路.
I.e. it vainly looks for names starting with the literal string "[0-9]". So as long as you keep using LINQ-to-SQL SqlMethods.Like
is the way to go.
在 Entity Framework 6.1.3(及更低版本)中,我们必须使用稍微不同的方式来获得相同的结果......
In Entity Framework 6.1.3 (and lower) we have to use a slightly different way to obtain the same result ...
return namesList.OrderBy(r => SqlFunctions.PatIndex("[0-9]%", c.Name) == 1) ...
... 因为 PatIndex
在 SqlFunctions 也支持范围模式匹配.
... because PatIndex
in SqlFunctions also supports range pattern matching.
但是在 Entity Framwork 6.2 中,由于新的 DbFunctions.Like
函数,我们重新使用 LINQ-to-SQL:
But in Entity Framwork 6.2 we're back on track with LINQ-to-SQL because of the new DbFunctions.Like
function:
return namesList.OrderBy(r => DbFunctions.Like(r.Name, "[0-9]%")) ...
最后,Entity Framework 核心也有一个 Like
功能:
Finally, also Entity Framework core has a Like
function:
return namesList.OrderBy(r => EF.Functions.Like(r.Name, "[0-9]%")) ...
这篇关于如何让 LINQ 执行(SQL)LIKE 范围搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!