带有Contains的LinqKit谓词或的计算结果为等于

Question

我正在为我的应用程序实现搜索。除非用户的搜索通过搜索相关实体级联，否则搜索会起作用。我已经调试了代码，并测试了实体框架生成的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));

Answer 1

它与LINQKit PredicateBuilder无关。原因是的结果

c.IndividualOrganisationGroups.Select(og => og.OrganisationGroup).Select(g => g.Group.Name)

是IEnumerable<string>(或IQueryable<string>(，所以您使用的是Enumerable(或Queryable(Contains方法，而不是其他地方的string.Contains方法。

您真正需要的不是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())