c# - LinqKit Predicate Or with Contains 计算为等于
问题描述
我正在搜索我的应用程序。搜索工作除了当用户的搜索通过搜索相关实体进行级联时。我已经调试了代码并测试了 Entity Framework 生成的 SQL。我发现问题是Contains()
在 SQL 中应该是“LIKE”时转换为“=”。Contains()
按照我对 FirstName、MiddleName 等的初始谓词的预期工作,但不在if (cascade)
代码块中。
我的 C# 搜索逻辑:
public IList<Individual> Find(string search, bool cascade, bool includeInactive)
{
_context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
IQueryable<Individual> query = _context.Individuals;
if (!string.IsNullOrWhiteSpace(search))
{
search = search.Trim();
string[] searchParts = search.Split(' ');
ExpressionStarter<Individual> predicate = PredicateBuilder.New<Individual>(false);
foreach (string searchPart in searchParts)
{
predicate = predicate.Or(c =>
c.FirstName.Contains(searchPart) || c.MiddleNames.Contains(searchPart) ||
c.LastName.Contains(searchPart) || c.PreferredName.Contains(searchPart));
if (cascade)
{
predicate = predicate.Or(c =>
c.IndividualOrganisationGroups.Select(og => og.OrganisationGroup).Select(g => g.Group.Name)
.Contains(searchPart));
}
}
query = query.Where(predicate);
}
if (!includeInactive)
{
query = query.Where(c => c.Active);
}
return query.ToList();
}
从 EF 生成的 SQL:
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[RegistrationTypeID] AS [RegistrationTypeID],
[Extent1].[Title] AS [Title],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleNames] AS [MiddleNames],
[Extent1].[LastName] AS [LastName],
[Extent1].[PreferredName] AS [PreferredName],
[Extent1].[RegistrationNumber] AS [RegistrationNumber],
[Extent1].[Username] AS [Username],
[Extent1].[AzureID] AS [AzureID],
[Extent1].[Notes] AS [Notes],
[Extent1].[Active] AS [Active],
[Extent1].[CreatedDate] AS [CreatedDate],
[Extent1].[CreatedBy] AS [CreatedBy],
[Extent1].[UpdatedDate] AS [UpdatedDate],
[Extent1].[UpdatedBy] AS [UpdatedBy]
FROM [dbo].[Individual] AS [Extent1]
WHERE ([Extent1].[FirstName] LIKE @p__linq__0 ESCAPE '~') OR ([Extent1].[MiddleNames] LIKE @p__linq__1 ESCAPE '~') OR ([Extent1].[LastName] LIKE @p__linq__2 ESCAPE '~') OR ([Extent1].[PreferredName] LIKE @p__linq__3 ESCAPE '~') OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[IndividualOrganisationGroup] AS [Extent2]
INNER JOIN [dbo].[OrganisationGroup] AS [Extent3] ON [Extent2].[OrganisationGroupID] = [Extent3].[ID]
INNER JOIN [dbo].[Group] AS [Extent4] ON [Extent3].[GroupID] = [Extent4].[ID]
WHERE ([Extent1].[ID] = [Extent2].[IndividualID]) AND (([Extent4].[Name] = @p__linq__4) OR (1 = 0))
))
-- p__linq__0: '%screen%' (Type = AnsiString, Size = 8000)
-- p__linq__1: '%screen%' (Type = AnsiString, Size = 8000)
-- p__linq__2: '%screen%' (Type = AnsiString, Size = 8000)
-- p__linq__3: '%screen%' (Type = AnsiString, Size = 8000)
-- p__linq__4: 'screen' (Type = AnsiString, Size = 8000)
有问题的 SQL(最后一个 WHERE 子句):
WHERE ([Extent1].[ID] = [Extent2].[IndividualID]) AND (([Extent4].[Name] = @p__linq__4) OR (1 = 0))
它应该是什么样子:
WHERE ([Extent1].[ID] = [Extent2].[IndividualID]) AND (([Extent4].[Name] LIKE @p__linq__4) OR (1 = 0))
所以我的问题是,如何在我的实体框架 SQL 中将此代码转换为 LIKE?
predicate = predicate.Or(c =>
c.IndividualOrganisationGroups.Select(og => og.OrganisationGroup).Select(g => g.Group.Name)
.Contains(searchPart));
解决方案
它与 LINQKit PredicateBuilder 无关。原因是结果
c.IndividualOrganisationGroups.Select(og => og.OrganisationGroup).Select(g => g.Group.Name)
is IEnumerable<string>
(or IQueryable<string>
),所以你正在使用Enumerable
(or Queryable
)Contains
方法而不是string.Contains
其他地方的 as。
而不是Select
+ Contains
,您真正需要的是Any扩展方法。
您的示例最简洁的语法是:
c => c.IndividualOrganisationGroups
.Any(og => og.OrganisationGroup.Group.Name.Contains(searchPart))
当然,如果您需要检查更多属性Group
(甚至不检查),请使用Select
+ Any
:
c => c.IndividualOrganisationGroups
.Select(og => og.OrganisationGroup.Group)
.Any(g => g.Name.Contains(searchPart))
或者通过以“正常”方式编写查询,将带有过滤器的集合转换为单个布尔值的最自然方式。例如Select
,Where
等等,并将无参数Any
放在最后:
c => c.IndividualOrganisationGroups
.Select(og => og.OrganisationGroup.Group)
.Where(g => g.Name.Contains(searchPart))
.Any())
推荐阅读
- email - 有什么方法可以禁用主题行的 UTF-8 编码?
- excel - VBA: Check if files exist
- c# - 如何发送带有 HTML 标签的电子邮件,而不是 C# 中的 HTML 纯文本
- javascript - 如何在下拉表单中添加 onchange 函数来发送数据?
- oracle - Oracle - 如何动态创建一个具有多个值的插入
- swift - 如何将 Realm 对象添加到 Set
- reactjs - 无法使用 React 中的 Promises 处理来自 API 调用的响应
- javascript - 使 socket.io 在 Node.js 的 API 端点内工作
- java - 将自纪元以来的毫秒数转换为 1582 年之前的日历日期时出现问题
- javascript - javascript - 如何将字符串拆分为对象数组