locked
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? 

    Wednesday, January 23, 2008 3:44 AM

Answers

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




    Wednesday, January 23, 2008 6:32 AM

All replies

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




    Wednesday, January 23, 2008 6:32 AM
  • 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.

    Monday, July 28, 2008 12:45 PM
  • 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

    Tuesday, July 29, 2008 3:40 PM
  •  

    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!

    Sunday, August 10, 2008 9:30 PM
  • 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

     

    Sunday, August 10, 2008 9:38 PM
  • 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
    Friday, November 06, 2009 5:09 AM
  • This is good You must get error if you get error it will give new solution to you. Try to solve your self. thank you.

    Wednesday, July 20, 2011 4:11 PM