C# – Why does this LINQ-to-SQL query get a NotSupportedException

clinqlinq-to-sql

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

Best Answer

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;
Related Topic