Linq To Sql搜索多列和多个单词

I am trying to do an autocomplete search using a webservice and Linq To Sql to access the database.

我正在尝试使用Web服务和Linq To Sql进行自动完成搜索以访问数据库。

Here is my code. This returns results that match any of the search terms, I would like to modify this so each result contains all of the search terms.

这是我的代码。这将返回与任何搜索词匹配的结果,我想修改它,以便每个结果包含所有搜索词。

I'm aware that SQL full text search is probably the most elegant solution, but I'd like to see if this functionality is possible without modifying the database.

我知道SQL全文搜索可能是最优雅的解决方案,但我想看看是否可以在不修改数据库的情况下实现此功能。

 string[] searchTerms = searchString.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToArray();
        IQueryable<AccountResult> results = db.cdAccounts
            .Select(x =>
                new AccountResult()
                {
                    idAccount = x.id_Account,
                    AccountName = x.AccountNme,
                    AccountNumber = x.AccountNum
                }).Distinct().OrderBy(x => x.AccountName);
        foreach (string searchTerm in searchTerms)
            results = results.Where(x => x.AccountName.Contains(searchTerm) || x.AccountNumber.Contains(searchTerm));

        return results.OrderBy(x => x.AccountName).Take(40).ToList();

2 个解决方案

#1

I ended up doing using linq to search for the fist term, then if there were multiple terms. I'm open to improvements/optimizations.

我最终使用linq搜索第一个术语,然后是否有多个术语。我愿意接受改进/优化。

//...
if (searchTerms.Count() > 1)
{
    List<string> remainingSearchTerms = new List<string>();
    for (int x = 1; x < searchTerms.Count(); x++)
        remainingSearchTerms.Add(searchTerms[x]);
    List<SearchResult> matchingResults = new List<SearchResult>();

    foreach (SearchResult result in allResults)
        if (MatchSearchTerms(new string[] { result.Name, result.Number, result.id.ToString() }, remainingSearchTerms.ToArray()))
            matchingResults.Add(result);

    return matchingResults.OrderBy(x => x.Name).Take(MaxResults).ToList();
}
else
    return allResults.OrderBy(x => x.Name).Take(MaxResults).ToList();
//...

private bool MatchSearchTerms(string[] searchFields, string[] searchTerms)
{
    bool match = true;
    foreach (string searchTerm in searchTerms)
    {
        if (match)
        {
            bool fieldMatch = false;
            foreach (string field in searchFields)
            {
                if (field.ToLower().Contains(searchTerm.ToLower()))
                {
                    fieldMatch = true; //Only one field needs to match the term
                    break;
                }
            }
            match = fieldMatch;
        }
        else
            break;
    }
    return match;
}

#2

I don't know linq to sql, but it looks like you are reassigning results in your for each loop when you should be appending to results.

我不知道linq到sql,但是当你应该追加结果时,看起来你正在为每个循环重新分配结果。

#1