在Linq中部分搜索空格分隔的值

本文关键字：分隔 空格 搜索 Linq 中部 | 更新日期: 2023-09-27 18:08:18

我有一个字段说公司名称，并希望部分搜索用户输入的数据，可能是空格分隔。假设用户输入"ABC India PVT Ltd"查询必须搜索包含一个或多个输入单词的所有记录。就像在SQL中，它是

Select * 
from Company 
where CompanyName like '%ABC%' or 
      CompanyName like '%India%' or 
      CompanyName like '%PVT%' or 
      CompanyName like '%Ltd%'

我正在尝试做这样的事情

string search = "ABC India PVT Ltd"
String[] searchArray = search.Split(' ');
IEnumerable<Account> accountInfo = acctInfo.Get(Filter: a
=>searchArray.AsQueryable().Contains(a.CompanyName));

但是这给了我与我想要达到的完全相反的结果。有什么方法可以做到这一点吗?

基本思想是通配符搜索，companyname字段的任何searchArray值。

a.CompanyName。

在Linq中部分搜索空格分隔的值

这就成功了。使用Any()筛选包含以下关键字之一的记录:

string searchTerm = "ABC India PVT Ltd";
string[] keywords = searchTerm.Split(' ');
List<Records> records = new List<Records>();
records.Add(new Records() { CompanyName = "Foo name" });
records.Add(new Records() { CompanyName = "ABC name" });
records.Add(new Records() { CompanyName = "Foo India" });
records.Add(new Records() { CompanyName = "PVT name" });
records.Add(new Records() { CompanyName = "Foo name" });
records.Add(new Records() { CompanyName = "Foobar" });
records.Add(new Records() { CompanyName = "Stackoverflow" });
var results = records.Where(x => keywords.Any(keyword => x.CompanyName
                                         .Contains(keyword))).ToList();

虚拟类:

public class Records
{
    public Records() { }
    public string CompanyName { get; set; }
}

将给您一个List<Record>的记录，其CompanyName包含任何关键字。如

  • "ABC名称"
  • 印度"Foo"
  • "PVT名称"

DGibbs提出的Any解决方案适用于少量元素(这可能是您的情况，所以我赞成)，但EF无法从此构造构建有效的查询。下面是只有4个元素的查询:

SELECT 
-- some fields
FROM [dbo].[Company] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        [UnionAll2].[C1] AS [C1]
        FROM  (SELECT 
            [UnionAll1].[C1] AS [C1]
            FROM  (SELECT 
                N'a' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            UNION ALL
                SELECT 
                N'b' AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
        UNION ALL
            SELECT 
            N'c' AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
    UNION ALL
        SELECT 
        N'd' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
    WHERE ( CAST(CHARINDEX([UnionAll3].[C1], [Extent1].[Name]) AS int)) > 0
)

这是不可伸缩的。超过一定数量的元素(只有几十个)，查询将抛出一个SQL异常，表示超过了最大嵌套级别。

为了防止这种情况，您应该使用OR子句构建查询。这就是PredicateBuilder可以提供帮助的地方:

var predicate = PredicateBuilder.False<Company>();
foreach (var keyword in searchArray)
{
    predicate = predicate.Or(c => c.Name.Contains(keyword));
}
var query = Companies.Where(predicate.Expand());

顺便说一下，有一个替代LINQKit的PredicateBuilder的源代码，它做同样的事情，但没有Expand。

相关文章：