none
LINQ with typed data not recognized RRS feed

  • Question

  • I created a class library of tableAdapters and I recently started looking at using LINQ to query from these data tables.

    I have no problem accessing the data with the Field function but when i try accessing the data directly using the strongly typed properties, none of them are recognized.

    Dim adapter as New LinkDefinitionsTableAdapter
    Dim tbl as LinkDefinitionsDataTable

    adapter.Fill(tbl)

    Dim query = From link in tbl _
    Where link.LINK_ID = 1 _

    But I get the error saying LINK_ID is not a member of System.Data.DataRow which makes me think that these tables are not strongly typed.  But isnt that inherent of table adapters, to return strongly typed datasets?  Am I missing something?


    Monday, June 29, 2009 4:56 PM

Answers

  • When using DataSet in threading situations you can do the following

         Have multiple threads doing reads

         Or

         Have a single thread doing a write

    I think one other thing you have to watch out for is how much data you are pulling in from the database. If its too big it might not make sense to use dataset as the caching mechanism here.

    Thanks,
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 2, 2009 6:28 PM

All replies

  • The query you are writing doesn't look correct to me. Below is a link to sample Linq queries

    http://msdn.microsoft.com/en-us/vbasic/bb738042.aspx

    Here is an example of querying for an untyped DataSet
    Public Sub DataSetLinq1()
        Dim numbers = TestDS.Tables("Numbers").AsEnumerable

        Dim lowNums = From r In numbers _
            Where r("number") < 5 _
            Select r

        Console.WriteLine("Numbers < 5:")

        For Each x In lowNums
            Console.WriteLine(x("number"))
        Next
    End Sub

    You will notice that there is a select used, where is your select?

    Just as a note when you are doing Linq over an untyped DataSet you would need to call AsEnumerable. In your case for a typedDataSet you will not have to do this. Also you should be able to have r.Number instead of the expression above as well.

    What type of table is tbl in your code above? Is it a TypedDataTable?

    Thanks
    Chris Robinson
    Program Manager - DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Chris Robinson- MSFT Monday, June 29, 2009 11:40 PM
    • Unproposed as answer by meriano Tuesday, June 30, 2009 12:38 AM
    Monday, June 29, 2009 8:43 PM
  • Hi

    Thanks for responding....

    I did not include the select statement because I am getting an error even before I write the select.  The problem is that the table is a typed data table (I have a dataset that was complied as a class library)  I use a tableadapter to fill a data table of type LinkDefinitionDataTable.  For some reason the IntelliSense is not picking up any of the column names that I would expect to see with a typed data table. 

    I did figure out after as I did more research that typed data sets do not require the .AsEnumerable and have since removed it.

    Another question I was trying to figure out was if I should even use LINQ in my application. 
    I have an application that is responsible for aqcuiring and converting up to 8 serial data links.  I use a different thread to acquire and convert the data for each link (so ultimately I could create up to 16 threads).  The conversion data for each link is stored in a database.  At the start of each converting thread, I use the tableadapter class to access the data needed for that link in particular.   I was looking at using LINQ to minimize the # of times and the periodicity of  connecting to the database.  I was thinking that at the beginning of the application, I could fill all the data tables with all the data and then use LINQ (in the converting threads) to query the dataset and retrieve only the information necessary for each link.  Would using LINQ here be an appropriate implementation.  If yes then I wonder, since the converting thread is a while loop that dequeues from a queue and converts the data frame based on the information  in the data table (currently),

    For each row as SensorDefRow in SensorDefTable
    ......takes the byte array and breaks out individual bytes and converts them to appropriate types based on info in data row
    Next

    would the same be done with the results of the LINQ query

    For Each sensorDef In query
        ....
    Next


    I understand that the execution of the LINQ query is done not during the dim of the query but as you enumerate through the results, correct?  So each data link could have 1000's of sensors and can be acquired up to 10x per second.  This was the reason for filling the datatable ahead, to avoid connecting to the database all the time. Is querying a dataset just as time consuming?  I apologize for all the questions, I am just trying to wrap my ahead around LINQ

    Thanks again

    Tuesday, June 30, 2009 1:02 AM
  • First off, anytime some says they are altering a dataset on multiple threads alarm bells are set off. DataSet is not threadsafe.  I just can't understand the scenario where you need to have 16 threads filling the data.

    I think Linq can be appropriate but Linq doesn't fill the data into the dataset, the TableAdapters do. The table adapters are not related to Linq at all.

    It seems you have some type of performance issue. I think you are asking if its better to use dataset or just query from the database. Assuming that there isn't too much data in the DataSet I would think the inmemory query will be faster.

    Now Linq is just a queryable interface. It is used in a number of different places. There is something called Linq to Sql, so what this does is it converts a Linq expression tree to a TSql command string. EntityFramework does the same thing. With DataSet Linq is used with in memory. It uses the Linq to objects. I hope this clear. For more information on Linq go here

    http://msdn.microsoft.com/en-us/netframework/aa904594.aspx

    Thanks
    Chris Robinson
    Program Manager
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 30, 2009 8:53 PM
  • I think I wasnt clear enough in my previous post.   I am not using the 16 threads to alter the dataset.  I am using 16 threads to acquire and convert the data, which is then written to an opc server, not to the database.  Each acquisition thread must retrieve information from the database in order to setup the acquisition of each serial link (such as baud rate and data frequency).  Each converting thread must also get information from the database to convert the data, which is in byte array, to individual parameters (such as location in the byte array, whether its an Int/UInt/Single, etc, and the parameters name). Also, the table adapter queries were parameterized to only get the information necessary for each thread.  The information retrieved does not change so I did all my queries on the database at the beginning of each thread.  Once the table adapter filled the table, the thread's while loop used the data in the local data table.  If the data did change, then the application would be restarted to in order to get the newest information.  (this app runs as a black box so there is not monitor/keyboard for user interaction so restarting was the customers request).   

    My question was...would it be appropriate to fill the dataset with all the information in the database so that each table contains the information for all links.  Then within my threads, use LINQ to query the dataset and bring in ONLY the necessary information needed for that particular thread.  Basically my converting thread runs like this currently

    Public Sub DataProcessing

    ' Fill local data tables with information for linkId=1
    adapter.FillByLink(sensorDetailsTable, linkId)


    While  True
    .....dequeue data frame  as byte array

    For each row in sensorDetailsTable.Rows

    'get info from table
    loc = row.Location
    name = row.Name
    type = row.type

    ...find data in byte array from loc
    ...convert to type
    ...store in dictionary by name

    Next

    ....Write all data in dictionary to opc server

    End While


    I wanted to change the converting thread sub so that it didnt actually access the database because if the database was unavailable, then each of the 8 converting threads would all get an error.  I figured if I filled the whole dataset before I even started the threads, that I would avoid any unnecessary errors but I was unsure of how to use the results of a LINQ query and what the performance was of using LINQ queries.   

    For example if the dataset was filled w/o errors and then each thread was started, can multiple threads do a query on the dataset at the same time to pull out the necessary info?   If the actual query execution is done when you enumerate thru the results, does this cause a performance issue if I am doing it 1000's of times a second? 

    Dim query = For sensors in sensorTable _
                       Where link.Field(Of Decimal)("LINK_ID") = 1 _
                       Select sensors

    For each sensor in query    'as i understand it the execution of the query is actually done here
    ....assign the variables
    ....do the conversion
    ....write to opc
    Next

    or would I use the CopyToTable function and use the table to loop as I do now?

    I hope I explained it better this time







     
    Wednesday, July 1, 2009 12:48 PM
  • When using DataSet in threading situations you can do the following

         Have multiple threads doing reads

         Or

         Have a single thread doing a write

    I think one other thing you have to watch out for is how much data you are pulling in from the database. If its too big it might not make sense to use dataset as the caching mechanism here.

    Thanks,
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 2, 2009 6:28 PM
  • Hi Chris,

    I have multiple threads doing reading

    how much data is considered too much for the efficiency of a data set?
    Wednesday, July 8, 2009 6:09 PM
  • DataSet can handle around a million rows but not well. How many rows do you have? How much memory is the data taking up? For example you can write the data to xml and get a guesstimate of the size by reviewing the data size of the xml file.

    Thanks
    Chris


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 8, 2009 9:33 PM
  • When you say a million rows, do you mean a million rows per table or a million rows across all tables in the dataset.  My concern is only with one table in my dataset that contains all the sensor definitions for the entire system.  At the high end, this table will hold about 300K-350K rows.  I can filter out most of this information before storing it in the dataset by only bringing in the data needed for my specific unit, if necessary.

    I cannot estimate the memory yet because I do not have a full database to extract from as of yet.
    Thursday, July 9, 2009 12:35 PM