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

Question

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?

Kirk Broadhurst · Answer 1 · 2011-11-18T16:08:38.127

I believe you can send a list of strings to LINQ-to-SQL for a contains.

I was wrong. But what you can do is make an enormous Where clause. I wouldn't recommend this if there are going to be a lot of strings to compare, but test and see.

var strings = TextBox1.Text.Split(' ').ToList();
var query = from product in dc.catalog select product;
foreach (var s in strings)
{
    query = query.Where(product => product.Name.Contains(s));
}
return query;

This will create something like

 var query = from product in dc.catalog
             where product.Name.Contains(string1)
             where product.Name.Contains(string2)
             where product.Name.Contains(string3)
             // etc
             select product;

It's pretty horrible, but if you only have a few strings it might do.

Update: To explain how the foreach loop works, consider the original query

 from product in dc.catalog select product

Let's say your textbox contains "Hello World". I'll split that into a list like { "Hello", "World" }, and then iterate the list.

First in the list is "Hello". The line query = query.Where(product => product.Name.Contains(s)); causes the expression to become

from product in dc.catalog select product
.Where(product => product.Name.Contains("Hello"))

It's not executed yet - it's just an expression tree - but the Where has been tagged on to the original query.

Second is "World", and the expression is appended

from product in dc.catalog select product
.Where(product => product.Name.Contains("Hello"))
.Where(product => product.Name.Contains("World"))

This does not read the same as "Contains Hello && World", but it is logically equivalent - the product will be tested to see if it contains "Hello", and if it does then it will be tested to see if it contains "World". It has to contain both to 'pass'.

Concatenating expressions like this is exactly the same as concatenating strings.

var letters = new List<string>(){ "H", "e", "l", "l", "o" };
string result = ""
foreach (var letter in letters)
{
    result = result + letter;
}

The value of result will not be "o". It will be "Hello".

@user776676 updated my answer. I don't know how to do 'strikethrough'! But the new answer should work... — Kirk Broadhurst, Nov 18 '11 at 04:33
Kirk, The number of keywords is variable, sometimes just 1, sometimes 3 or 4. If I can figure out how to do the foreach for the where clause within the query, that'd solve the problem, I think. Thanks. — user776676, Nov 18 '11 at 05:34
@user776676 Doesn't this answer solve that? loop through the strings, and add a `Where` for each of them. — Kirk Broadhurst, Nov 18 '11 at 06:19
No, Kirk, it doesn't. It just returns the records with the LAST keyword in string. As you can see in the foreach, query is reassigned after each iteration to contain the latest string s while I need all keywords to be present in the records. — user776676, Nov 18 '11 at 06:35
@user776676 In the foreach `query` is reassigned to *itself* **plus** the latest string. It will have `Where` clause for every string. I have expanded my answer - did you even try this?? — Kirk Broadhurst, Nov 18 '11 at 16:07

Chris Sainty · Accepted Answer · 2011-11-18T05:41:41.807

1

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 :)

edited Nov 18 '11 at 05:41

answered Nov 18 '11 at 03:52

Chris Sainty

9,086
1
26
31

Thanks, Chris. I tried but the query you proposed returns only the set of records that contains the LAST keyword only. If TextBox1 contains "red table round" then the records containing "round" are returned while I want the records that contain ALL 3 keywords. – user776676 Nov 18 '11 at 04:40
Ahh, the old lambda scope trick. You will need to make a copy of the string that is being passed into the Where clause. I'll update my answer. – Chris Sainty Nov 18 '11 at 05:39
@ChrisSainty the easy way around that is to materialize the `Split` as a list of strings. – Kirk Broadhurst Nov 18 '11 at 06:19

score 1 · Answer 3 · answered Nov 18 '11 at 11:08

I think a simple way could be executing the query on the objects, instead of transport it to the SQL.

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

It's not the best in terms of performance, but it depends on the size of the datasource.

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

3 Answers3