Why does this LINQ-to-SQL query get a NotSupportedException?

Question

The following LINQ statement:

public override List<Item> SearchListWithSearchPhrase(string searchPhrase)
{
    List<string> searchTerms = StringHelpers.GetSearchTerms(searchPhrase);

    using (var db = Datasource.GetContext())
    {
        return (from t in db.Tasks
                where searchTerms.All(term => 
                    t.Title.ToUpper().Contains(term.ToUpper()) &&
                    t.Description.ToUpper().Contains(term.ToUpper())) 
                select t).Cast<Item>().ToList();
    }
}

gives me this error:

System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

Looking around it seems my only option is to get all my items first into a generic List, then do a LINQ query on that.

Or is there a clever way to rephrase the above LINQ-to-SQL statement to avoid the error?

ANSWER:

Thanks Randy, your idea helped me to build the following solution. It is not elegant but it solves the problem and since this will be code generated, I can handle up to e.g. 20 search terms without any extra work:

public override List<Item> SearchListWithSearchPhrase(string searchPhrase)
{
    List<string> searchTerms = StringHelpers.GetSearchTerms(searchPhrase);

    using (var db = Datasource.GetContext())
    {

        switch (searchTerms.Count())
        {
            case 1:
                return (db.Tasks
                     .Where(t =>
                         t.Title.Contains(searchTerms[0])
                         || t.Description.Contains(searchTerms[0])
                         )
                     .Select(t => t)).Cast<Item>().ToList();
            case 2:
                return (db.Tasks
                     .Where(t =>
                         (t.Title.Contains(searchTerms[0])
                         || t.Description.Contains(searchTerms[0]))
                         &&
                         (t.Title.Contains(searchTerms[1])
                         || t.Description.Contains(searchTerms[1]))
                         )
                     .Select(t => t)).Cast<Item>().ToList();
            case 3:
                return (db.Tasks
                     .Where(t =>
                         (t.Title.Contains(searchTerms[0])
                         || t.Description.Contains(searchTerms[0]))
                         &&
                         (t.Title.Contains(searchTerms[1])
                         || t.Description.Contains(searchTerms[1]))
                         &&
                         (t.Title.Contains(searchTerms[2])
                         || t.Description.Contains(searchTerms[2]))
                         )
                     .Select(t => t)).Cast<Item>().ToList();
            default:
                return null;
        }
    }
}

Answer 1

Ed, I've run into a similiar situation. The code is below. The important line of code is where I set the memberList variable. See if this fits your situation. Sorry if the formatting didn't come out to well.

Randy

// Get all the members that have an ActiveDirectorySecurityId matching one in the list.
IEnumerable<Member> members = database.Members
   .Where(member => activeDirectoryIds.Contains(member.ActiveDirectorySecurityId))
   .Select(member => member);

// This is necessary to avoid getting a "Queries with local collections are not supported"
//error in the next query.    
memberList = members.ToList<Member>();

// Now get all the roles associated with the members retrieved in the first step.
IEnumerable<Role> roles = from i in database.MemberRoles
   where memberList.Contains(i.Member)
   select i.Role;