none
Local sequence cannot be used error - joining DataTable with LINQ to SQL datatable RRS feed

  • Question

  • This is my first attempt at LINQ programming, and boy, do I need some handholding! Could anyone tell my why this query works:

    var products = from myTable in myDT.AsEnumerable() select myTable;
    var myData = from p in products
      join d in db.tblicInventory on p.Field<string>("strProductID") equals d.strProductID
      select p;
    

    while this one does not:

    var products = from myTable in myDT.AsEnumerable() select myTable;
    var myData2 = from d in db.tblicInventory
      join tbl in products on d.strProductID equals tbl.Field<string>("strProductID")
      select d;

    In both cases, db.tblICInventory was created via LINQ to SQL and myDT is a DataTable. 

    When I evaluate MyData, it shows as as an iEnumerable containing a collection of DataRows.

    When I evaluate MyData2, it shows the following error:

    "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator."

    Any input is greatly appreciated.

    Thanks,

    Steve.

    Tuesday, May 10, 2011 4:25 PM

Answers

  • Hi Steve;

    To your question, "Have you any suggestion how I can rewrite the query to return all of the records from tblICInventory where tblICInventory.strProductID = myDTstrproductID?"

    The following queries will return all the records in tblICInventory table that have matching strProductID values in the myDT data table.

    // This query will return all the values for the column strproductID 
    // in the data table as a List<String>
    var prodIDs = (from pid in myDT.AsEnumerable()
            select pid.Field<string>("strproductID")).ToList();
            
    // Now we can query the SQL server using the list from the above query
    // to get the needed results
    var myData = from d in db.tblicInventory
           where prodIDs.Contains(d.strProductID)
           select d;
    
    // Now this works because Linq to SQL takes the list prodIDs from the
    // first query and builds a parameter list to be used in SQL server
    // The SQL sent to SQL Server looks something like the following
    
    SELECT [t0].[tblicInventory], ... , [t0].[LastColumn]
    FROM [dbo].[tblicInventory] AS [t0]
    WHERE [t0].[strProductID] IN (@p0, @p1, ... , @pn)
    
    -- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value 1]
    -- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value 2]
    -- ...
    -- @pn: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value n] 
    

    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by steveosmith Wednesday, May 11, 2011 2:46 PM
    Wednesday, May 11, 2011 4:35 AM

All replies

  • Hi Steve;

    The reason why this query works is that when you are doing the join this part, db.tblicInventory, is a complete query in itself, meaning that when it gets to this part of the query a SQL query is sent to the server to return all records from the tblicInventory table, and no other information from the outer query is used to formulate that SQL query. The locally the results of db.tblicInventory query is used to join with the local collection.

    var products = from myTable in myDT.AsEnumerable() select myTable;
    var myData = from p in products
           join d in db.tblicInventory on p.Field<string>("strProductID") equals d.strProductID
           select p;
    

    Where as in the following query, The outer query which uses the SQL database is dependent on the local collection to complete the query. But if a SQL statement could be formulated from the below query and sent to the SQL server to be executed there it has no knowledge of the products collection and would throw an exception. This is why this query does not work.

    var products = from myTable in myDT.AsEnumerable() select myTable;
    var myData2 = from d in db.tblicInventory
           join tbl in products on d.strProductID equals tbl.Field<string>("strProductID")
           select d;
    

    Fernando

     

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, May 10, 2011 8:58 PM
  • Thanks, Fernando, for explaining that. Makes sense. So - here's where the "handholding" comes in. Have you any suggestion how I can rewrite the query to return all of the records from tblICInventory where tblICInventory.strProductID = myDTstrproductID?

     

    Thanks again!

    S.

    Tuesday, May 10, 2011 9:48 PM
  • Hi Steve;

    To your question, "Have you any suggestion how I can rewrite the query to return all of the records from tblICInventory where tblICInventory.strProductID = myDTstrproductID?"

    The following queries will return all the records in tblICInventory table that have matching strProductID values in the myDT data table.

    // This query will return all the values for the column strproductID 
    // in the data table as a List<String>
    var prodIDs = (from pid in myDT.AsEnumerable()
            select pid.Field<string>("strproductID")).ToList();
            
    // Now we can query the SQL server using the list from the above query
    // to get the needed results
    var myData = from d in db.tblicInventory
           where prodIDs.Contains(d.strProductID)
           select d;
    
    // Now this works because Linq to SQL takes the list prodIDs from the
    // first query and builds a parameter list to be used in SQL server
    // The SQL sent to SQL Server looks something like the following
    
    SELECT [t0].[tblicInventory], ... , [t0].[LastColumn]
    FROM [dbo].[tblicInventory] AS [t0]
    WHERE [t0].[strProductID] IN (@p0, @p1, ... , @pn)
    
    -- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value 1]
    -- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value 2]
    -- ...
    -- @pn: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [Value n] 
    

    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by steveosmith Wednesday, May 11, 2011 2:46 PM
    Wednesday, May 11, 2011 4:35 AM
  • Fernando, it's so simple when you explain it! I'd fought with Contains to no avail. I think I'd tried "Where D.Contains". Anyway, thanks again for your assistance. One of these days - if I ever get time - I'll have to sit down with a good tutorial on LINQ. 

     

    Regards,

    Steve.

    Wednesday, May 11, 2011 2:45 PM
  • Not a problem Steve, glad I was able to help.

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, May 11, 2011 2:58 PM
  • You're going to regret ever answering my original question! The query works perfectly - until myDT get larger than 2100 items. Then I get the following error when trying to evaluate MyData.

    "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100."

    I'm just about ready to write myDT to the SQL database and use a T-SQL JOIN statement to return everything. :-(

    Wednesday, May 11, 2011 4:49 PM
  • OK, I solved this one myself - I think. I loaded a data table with tblICInventory from the database. I then used that data table as my source for ProdIDs. Now it can load up as many rows as it needs. 

    S.

    Wednesday, May 11, 2011 6:14 PM
  • Hi Steve;

    Be aware that this Linq query passes the prodIDs, from my last post, to SQL server as individual parameters and ADO .Net NOT Linq has a limit on the size of this type as you noted 2100 items. There fore if myDT has more then 2100 item you will need to do it in multiple Linq queries so that this exception is avoided.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, May 11, 2011 7:39 PM