none
Query help nested IN query getting error: Queries with local collections are not supported RRS feed

  • Question

  • var productIDs = from p in db.Products where p.ID == productID || p.ParentID == productID select p.ID;  
     
    var fileinfoIDs = from d in db.DocumentProductFiles where productIDs.Contains(d.ProductID) select d.FileInfoID;  
     
    var files = from f in db.DocumentFileInfos 
     
    where fileinfoIDs.Contains(f.ID) where f.CategoryID == categoryID orderby f.DateUpdated descending select f; 

    I am relatively new to Linq and trying to convert a project over. I am getting the eror in the subject with the code listed below. What i am trying to do is:

    Given a Product ID and Category ID retrieve all listings in the file table for the Given Product and it's children for the given category.

    The table structure is many-to-many.

    Products -- DocumentProductFiles(Join of Products and DocumentFileInfos PKS) and DocumentFileInfos

    So i tried (above) to construct a query that retrieves the IDs of the specified product and it's children (works)
    Based on query 1 results get the retireve the IDs of all fileinfo objects that are linked to the product and it's children(works)> Then using the set of fileinfo IDs retrieved in the 2nd query retrieve the list of fileinfos for the product and its children in the specified category (Fails with error).

    I plugged this all into linqpad and i can successfully retrieve a resultset for query 1 and 2. Can anyone help me rewrite the query so to achieve the desired results?




    Wednesday, January 28, 2009 9:39 PM

All replies

  • Kevin2005 said:

    var productIDs = from p in db.Products where p.ID == productID || p.ParentID == productID select p.ID;  
     
    var fileinfoIDs = from d in db.DocumentProductFiles where productIDs.Contains(d.ProductID) select d.FileInfoID;  
     
    var files = from f in db.DocumentFileInfos 
     
    where fileinfoIDs.Contains(f.ID) where f.CategoryID == categoryID orderby f.DateUpdated descending select f; 



     



    Try changing "where fileinfoIDs.Contains(f.ID) where f.CategoryID == categoryID" to "where fileinfoIDs.Contains(f.ID) && f.CategoryID == categoryID".

    Is d.FileInfoID nullable? You may need to cast it to non nullable.

    That aside, you can do all of that in one expression (resulting in a single db roundtrip) if you want. See my reply in your other thread.

    Thursday, January 29, 2009 5:30 AM
    Answerer
  • This is likely a bug in LINQ to SQL.  Have you tried writing the query an a single expression instead of broken into separate variables.  My guess is that L2S is confusing a local variable containing a piece of the query as a local collection.
    Wayward LINQ Lacky
    Thursday, January 29, 2009 4:51 PM
    Moderator