Search code examples
asp.netlinqlinqdatasource

LINQ: How to rewrite WHERE clause to avoid: Local sequence cannot be used in LINQ to SQL

My Linq query gives the error: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

 var query = from product in dc.catalog
              where TextBox1.Text.Split(' ').All(s => product.Name.Contains(s))
              select product;

How can it be rewritten to avoid this error?

Solution

  • As the error says, only contains is supported. Your list in turned into a SQL IN clause.
    To do what you are after, you are going to need to rely on the deferred execution LINQ provides, and build up a LINQ statement that checks every word is in the name.

    var query = dc.catalog.AsQueryable();
foreach(var s in TextBox1.Text.Split(' ') {
  string copy = s;  // Take a local copy of the string. Lambda's inside loops are fun!
  query= query.Where(product => product.Name.Contains(copy));
}

    Edit: Taking a local copy of the string to hopefully get around the scoping issue on the lambda. Compiled in my head at 5pm on a Friday, my apologies if it still isn't right :)