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;

Answer 2

Since you cannot join local sequence with linq table, the only way to translate the above query into SQL woluld be to create WHERE clause with as many LIKE conditions as there are elements in searchTerms list (concatenated with AND operators). Apparently linq doesn't do that automatically and throws an expception instead. But it can be done manually by iterating through the sequence:

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

    using (var db = Datasource.GetContext())
    {
        IQueryable<Task> taskQuery = db.Tasks.AsQueryable();
        foreach(var term in searchTerms)
        {
              taskQuery = taskQuery.Where(t=>t.Title.ToUpper().Contains(term.ToUpper()) && t.Description.ToUpper().Contains(term.ToUpper()))            
        }
        return taskQuery.ToList();
    }
}

Mind that the query is still executed by DBMS as a SQL statement. The only drawback is that searchTerms list shouldn't be to long - otherwise the produced SQL statement won'tbe efficient.