实体框架 EF.function

我正在阅读实体框架核心2.0 https://blogs.msdn.microsoft.com/dotnet/2017/08/14/announcing-entity-framework-core-2-0/的公告

它说他们为执行 SQLLIKE操作添加了新的 SQL 函数，如 EF.Functions.Like。

我想知道，那么 EF.Functions.Like和 string.Contains/StartsWith之间的区别是什么？

例如:

var customers = context.Customers.Where(c => c.Name.StartsWith("a")); // Version A
var customers = context.Customers.Where(c => EF.Functions.Like(c.Name, "a%")); // Version B

这两个版本有什么不同？ EF 已经知道如何将 string.Contains/StartsWith转换为相应的 SQL 操作，不是吗？

我能想到的唯一原因就是 EF 函数。Like 允许使用更复杂的模式，如 "a%b%"(尽管这个模式可以写成 StartsWith("a") && Contains("b"))

这就是原因吗？

96834 次浏览
小开
最佳答案

Like query supports wildcard characters and hence very useful compared to the string extension methods in some scenarios.

For ex: If we were to search all the 4 lettered names with 'ri' as the middle characters we could do EF.Functions.Like(c.Name, "_ri_");

or to get all the customers from cities which start with vowels:

var customers = from c in context.Customers
where EF.Functions.Like(c.City, "[aeiou]%")
select c;

(Please read @Tseng's answer on how they are translated differently into SQL queries)

小开

The answer of @adiga is quite incomplete and covers just a part of the differences in usage.

However, .StartsWith(...), .Contains(...) and .EndsWith(...) are also translated differently into SQL then EF.Functions.Like.

For example .StartsWith gets translated as (string LIKE pattern + "%" AND CHARINDEX(pattern, string) = 1) OR pattern = '' where .Contains gets translated into (CHARINDEX(pattern, string) > 0) OR pattern = ''.

EF.Functions.Like however gets translated into string LIKE pattern [ESCAPE escapeChar].

This may also have implications on Performance. The above is valid for EF Core SqlServer provider. Other EF Core providers may translate it differently.


提交答案