Linq To Sql Search Multiple Columns and Multiple Words

Question

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.

Thanks in advance!

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

Answer 1

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.

Answer 2

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

Answer 3

Take a look at http://stackoverflow.com/questions/1866018/how-can-i-make-this-linq-search-method-handle-more-than-two-terms