Need linq help

Question

I am getting the following error:

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

from the following linq statement:

var products = from p in db.Products

  from s in search.Split(new char[] { ',' })

  where p.title.Contains(s) || p.description.Contains(s) || p.tags.Contains(s)

  select p;

What's the deal? 

Answer 1

You cannot run this query since one of your sources s is an in-memory array. The problem is that this array should somehow be  passed to SQL Server for which there is no direct solution. As it is said the only part of the query in which you can pass  a sequence is in Contains method calls.

So, as far as I know, you have two options:

• fetch all products first and then perform your filtering in memory. This way, you can have a query with the same syntax as your current one. What you lose of course is performance.
• reformulate your query as follows:

IQueryable<Product> query = null;

foreach (var s in search.Split(','))
{

var q = from p in db.Products
           where p.titles.Contains(s) || p.description.Contains(s) || p.Tags.Contains(s)
           select p;

if (query == null)

query = q;

else

query = query.Union(q);

}

Answer 2

I believe I saw a solution to this dilemma over at linqhelp.com. I will look for it and post it up once I find it.

Answer 3

CompuBoy is not correct, you can join the string array to the products in your database if you state the in memory array first in the query. However, that will cause the full list of products to be sent to the client and LINQ to Objects will evaluate the join.

 

As a better option, create the string array first and then use the .Contains method on the array (not the item you are searching on). As a result, your array will be sent as separate parameters to your TSQL query and translated into an IN clause. Here's some revised code:

 

Code Snippet

string[] searches = search.Split(new char[] {","});

var products = from p in db.Products

                      where searches.Contains(p.title) ||

                                searches.Contains(p.description) ||

                                searches.Contains(p.tags)

                      select p;

 

 

 

 

Notice the difference in the Where clause here. In your case, you were trying to find instances where the title, description or tags strings contained the search array, rather than finding where the search array contained one of your target strings.

 

Jim Wooley

www.ThinqLinq.com

Answer 4

 

Hi Jim,

 

I have looked at your solution and have one quick question.

 

Let's say I have a database of books and there is a book titled "Linq for idiots".  If the user types in "Linq" as a search string, will "Linq for idiots" be returned?  It seems that your example tries to evalute whether "Linq" contains "Linq for idiots", and it will certainly return false.  Any insight would be greatly appreciated!

Answer 5

Ah, I missed that part of the requirement. In that case you want the TSQL incluiding a combination of an IN clause with a LIKE. I don't believe this is possible in TSQL, let alone through LINQ. In this case, you may need to dynamically build the where expression manually as a set of OR clauses.

 

Jim Wooley

www.ThinqLinq.com

 

Answer 6

If you're dataset is not too large you can dump the Table to a List<T>

var Query = from p in Db.Products.ToList()

  ...

          select ....

---

Regards, Mike DePouw