如何在EF Core中同时使用Collate与SQL Server Replace等效功能

问题背景 #

我拥有Produtos和Indice两个实体，两者为多对多关系，Produtos实体包含用于搜索的Indice关键词列表。当前搜索代码可正常运行，但需要添加类似SQL Server REPLACE函数的功能，让搜索时忽略字符-——例如搜索TRex或T-Rex时，都能返回包含TRex和T-Rex的记录。

此前尝试用存储过程实现，但因需支持多关键词“AND”匹配（搜索词按空格拆分后需全部匹配），调用逻辑过于复杂，故寻求更简洁的解决方案。

当前生成的SQL代码 #

SELECT aLotaStuff
FROM (
    SELECT OtherLottaStuff
    FROM [Produtos] AS [p]
    WHERE (([p].[EhKit] = CAST(1 AS bit)) AND (([p].[ValorUnitario] > @__p_0) AND ([p].[ValorUnitario] < @__p_1))) AND EXISTS (
        SELECT 1
        FROM [ProdutoIndice] AS [p0]
        INNER JOIN [Indice] AS [i] ON [p0].[IndiceId] = [i].[Id]
        WHERE ([p].[Id] = [p0].[ProdutosId]) AND ([i].[PalavraChave] COLLATE Latin1_General_CI_AI = @__Replace_3))
    ORDER BY [p].[Id]
    OFFSET @__p_4 ROWS FETCH NEXT @__p_5 ROWS ONLY
) AS [t]
LEFT JOIN (
    SELECT [c].[CategoriasId], [c].[ProdutosId], [c0].[Id], [c0].[CategoriaPaiId], [c0].[GomecCategoriaId], [c0].[GomecCategoriaPaiId], [c0].[Nome], [c0].[Url]
    FROM [CategoriaProduto] AS [c]
    INNER JOIN [Categorias] AS [c0] ON [c].[CategoriasId] = [c0].[Id]
) AS [t0] ON [t].[Id] = [t0].[ProdutosId]
LEFT JOIN [FotosProdutos] AS [f] ON [t].[Id] = [f].[ProdutoId]
ORDER BY [t].[Id], [t0].[CategoriasId], [t0].[ProdutosId], [t0].[Id]

当前使用的EF Core代码 #

var produtos = _dbContext.Produtos.AsQueryable();
produtos = produtos.Where(x => x.EhKit == true).Where(x => x.ValorUnitario > valMin && x.ValorUnitario < valMax);
if (!string.IsNullOrEmpty(searchTerm))
{
    var searchTerms = searchTerm.Split(' ');
    foreach (var term in searchTerms)
    {
        if (term.Length > 1 && term != "de")
        {
            //produtos = produtos.Where(p=>p.Indice.Any(ind=>ind.PalavraChave == term));
            produtos = produtos.Where(p => p.Indice.Any(ind => EF.Functions.Collate(ind.PalavraChave, "Latin1_General_CI_AI") == term));
        }
    }
}

解决方案 #

只需修改EF Core中关键词匹配的逻辑，对数据库中的PalavraChave和搜索词同时去掉-字符后再进行比较，同时保留原有的大小写/重音不敏感规则。修改后的代码如下：

var produtos = _dbContext.Produtos.AsQueryable();
produtos = produtos.Where(x => x.EhKit == true).Where(x => x.ValorUnitario > valMin && x.ValorUnitario < valMax);
if (!string.IsNullOrEmpty(searchTerm))
{
    var searchTerms = searchTerm.Split(' ');
    foreach (var term in searchTerms)
    {
        if (term.Length > 1 && term != "de")
        {
            // 对双方的字符串都去掉'-'后再做匹配
            var cleanedTerm = term.Replace("-", "");
            produtos = produtos.Where(p => p.Indice.Any(ind => 
                EF.Functions.Collate(EF.Functions.Replace(ind.PalavraChave, "-", ""), "Latin1_General_CI_AI") 
                == cleanedTerm
            ));
        }
    }
}

说明 #

1. 代码中通过EF.Functions.Replace调用SQL Server的REPLACE函数，去除数据库字段PalavraChave中的-；同时提前处理搜索词，去掉其中的-。
2. 保留了原有的Latin1_General_CI_AI排序规则，确保大小写和重音不敏感的匹配。
3. 原有的多关键词“AND”逻辑不受影响——拆分后的每个关键词都会生成一个WHERE条件，确保所有关键词都匹配才会返回结果。

内容的提问来源于stack exchange，提问作者user6836071