如何使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类中将其实现到Domain Project，因此对于某些功能，它将是一个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());

但是它告诉我，表达式不包含"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]%")) ...

此

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]")) ...

...将翻译成废话:

... would translate to nonsense:

[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"rel =" nofollow noreferrer> SqlFunctions 还支持范围模式匹配.

... because PatIndex in SqlFunctions also supports range pattern matching.

但是在实体框架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屋！