EntityFramework: Linq on SQL: Contains or IndexOf?

Question

我对带有.NET 4.5(C#(的EntityFramework 6有一个奇怪的情况。

我在两个不同的地方(几乎(有相同的查询。但一次它再次查询数据库，第二次查询内存中对象。由于我正在过滤子字符串，因此这是一个至关重要的区别：

数据库结构包括表角色、右表和跨表Role_Right

第一次我想找到尚未分配给角色的所有可用权限以及(这就是它变得复杂的地方(手动过滤器以减少结果列表：

Role role = ...;
string filter = ...;
var roleRightNames = role.Right.Select(roleRight => roleRight.RightName);
var filteredRights = context.Right.Where(right => !roleRightNames.Contains(right.RightName));
if (!string.IsNullOrWhiteSpace(filter))
{
filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var result = filteredRights.ToList();

我无法使用IndexOf(filter, StringComparison.InvariantCultureIgnoreCase) >= 0)因为这无法转换为 SQL。但我对Contains很好，因为它产生了所需的结果(见下文(。

启用 SQL 输出时，我得到：

SELECT [Extent1].[RightName] AS [RightName]
FROM [dbo].[Right] AS [Extent1]
WHERE ( NOT ([Extent1].[RightName] IN ('Right_A1', 'Right_A2', 'Right_B1'))) AND ([Extent1].[RightName] LIKE @p__linq__0 ESCAPE '~'
-- p__linq__0: '%~_a%' (Type = AnsiString, Size = 8000)

这正是我想要的，在过滤器"_a"上进行不区分大小写的搜索以查找例如"Right_A3">

第二次我想过滤同一过滤器的现有关联权限：

Role role = ...;
string filter = ...;
var filteredRights = string.IsNullOrWhiteSpace(filter)
? role.Right
: role.Right.Where(e => e.RightName.IndexOf(filter, StringComparison.InvariantCultureIgnoreCase) >= 0);            
var result = filteredRights.ToList();

这次它迫使我使用IndexOf，因为它使用string的Contains方法，而不是将其转换为SQLLIKE并且string.Contains区分大小写。

我的问题是我无法 - 通过查看代码 - 预测何时对数据库执行查询以及何时在内存中完成查询，并且由于我无法在第一个查询中使用IndexOf，在第二个查询中使用Contains这对我来说似乎有点不可预测。当有一天首先执行第二个查询并且数据尚未在内存中时，会发生什么情况？

编辑10 Feb 2020

好的，所以我弄清楚了主要区别是什么。context.Right属于DbSet类型，它是一个IQueryable，后续的扩展方法也是如此Where。但是，userRole.Right返回一个ICollection，这是一个IEnumerable，后续Where也是如此。有没有办法使实体对象的关系属性成为IQueryable？AsQueryable没有用。这意味着在执行内存中Where之前，始终从数据库中获取所有关联的Right实体。 我们不是在谈论大量的数据，至少现在这种行为是可以预测的，但我仍然觉得很不幸。

Answer 1

我的问题是我无法 - 通过查看代码 - 预测何时 对数据库执行查询，并在内存中执行查询时执行查询 并且由于我不能在第一个查询中使用 IndexOf 并在 其次，这对我来说似乎有点不可预测。

您可以在两个查询中使用IndexOf和Contains，只要不使用具有StringComparison的重载即可。正如@BrettCaswell所指出的，大小写匹配是通过数据库/表/列的排序规则固定的。 如果查询的根是上下文的DbSet，并且所有方法调用都可以转换为 SQL，则查询将被转换为 SQL。

一旦无法转换方法，就会在 SQL 级别执行当前状态请求，并在 .Net 应用程序的内存中执行查询的其余部分。

此外，我认为应该p__linq__0值'%~_a%'因为_是LIKE子句中的特殊字符。

Answer 2

好的，所以我找到了两种不同的解决方案，以便在关系包含巨大的结果集的情况下始终查询数据库。这两种解决方案都不是直接直观的 - 恕我直言 - 您将需要以前不需要的DbContext变量。

解决方案一是使用Role表作为起点，只需筛选具有正确 ID 的实体。注意您不能使用Single因为这样您处理单个实体对象，然后您又回到了开始的位置。您需要先使用Where，然后使用SelectMany，即使它是违反直觉的：

Role role = ...;
string filter = ...;
var filteredRights = context.Role.Where(e => e.RoleId == userRole.RoleId).SelectMany(e => e.Right);
if (!string.IsNullOrWhiteSpace(filter))
{
filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var rights = filteredRights.ToList();

这会导致针对数据库的 SQL 查询：

SELECT 
[Extent1].[RightName] AS [RightName]
FROM [dbo].[Role_Right] AS [Extent1]
WHERE ([Extent1].[RoleId] = @p__linq__0) AND ([Extent1].[RightName] LIKE @p__linq__1 ESCAPE '~')
-- p__linq__0: '42' (Type = Int32, IsNullable = false)
-- p__linq__1: '%~_a%' (Type = AnsiString, Size = 8000)

我在这里找到的第二个解决方案：https://stackoverflow.com/a/7552985/2334520

就我而言，这会导致：

Role role = ...;
string filter = ...;
var filteredRights = context.Entry(userRole).Collection(e => e.Right).Query();
if (!string.IsNullOrWhiteSpace(filter))
{
filteredRights = filteredRights.Where(e => e.RightName.Contains(filter));
}
var rights = filteredRights.ToList();

和 SQL

SELECT 
[Extent1].[RightName] AS [RightName]
FROM [dbo].[Role_Right] AS [Extent1]
WHERE ([Extent1].[RoleId] = @EntityKeyValue1) AND ([Extent1].[RightName] LIKE @p__linq__0 ESCAPE '~')
-- EntityKeyValue1: '42' (Type = Int32, IsNullable = false)
-- p__linq__0: '%~_a%' (Type = AnsiString, Size = 8000)