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.

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

I thought of a less than ideal solution, now I search with Linq for the first search term past to the method, and if there is more than 1 search term, I use the initial results from the db and filter results that don't match the second, third, etc terms. Its a little ugly, but its fast enough. Still open to suggestions. — KClough
– KClough, Commented May 22, 2009 at 14:11

KClough · Accepted Answer · 2009-06-16 17:25:58Z

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

Tim Hoolihan · Accepted Answer · 2009-05-22 13:19:39Z

0

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.

answered May 22, 2009 at 13:19

Tim Hoolihan

12.4k3 gold badges43 silver badges54 bronze badges

1 Comment

KClough Over a year ago

I'm trying to filter down the results. Pseudo code: First Loop: Results.Where x contains '123' Second Loop Results(Where x contains '123')(Where x Contains '456') Does that make sense?

Collectives™ on Stack Overflow

Linq To Sql Search Multiple Columns and Multiple Words

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related