CODEWITHSUNDEEP
c#linqlinq-to-sql
KClough
KClough

Reputation: 2089

Linq To Sql Search Multiple Columns and Multiple Words

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

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.

 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();

Upvotes: 4

Views: 8170

Answers (2)

KClough
KClough

Reputation: 2089

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

//...
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;
}

Upvotes: 1

Tim Hoolihan
Tim Hoolihan

Reputation: 12396

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.

Upvotes: 0

Related Questions