What is the correct way to dynamically add an undetermined number of clauses to a Linq 2 Sql query?

Question

This function is used to return a contact list for a users search input. The number of search terms is always at least one, but could be many.

public IList<Contact> GetContacts(string[] searchTerms)
{
    using (dbDataContext db = new dbDataContext())
    {
        var contacts = from _contacts in db.Contacts
                       orderby _contacts.LastName ascending, _contacts.FirstName ascending
                       select _contacts;

        foreach (string term in searchTerms)
        {
            contacts = (IOrderedQueryable<Contact>)contacts.Where(x => SqlMethods.Like(x.FirstName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.MiddleName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.LastName, "%" + term + "%")
                                                                    || SqlMethods.Like(x.PreferredName, "%" + term + "%"));
        }

        return contacts.ToList<Contact>();
    }
}

The problem is in the loop. Only the last search term is ever used, even though the generated sql looks correct (as in the correct amount of clauses are generated for the number of terms).

Example - if I pass two terms ('andr','sm'), the sql generated shows two blocks of clauses as expeted, but only uses 'sm' as the param in both blocks.

What am I doing wrong? Should I even be using SqlMethods?

Answer 1

Maybe the problem is with capturing the loop variable term. Try this:

foreach (string term in searchTerms) 
{
    string t = term;  
    contacts = ... // use t instead of term
}