CopyToDataTable(Of DataRow) shows syntax error

Question

Im trying to convert the output of a LINQ query to a datatable, I have the following code but it shows a syntax error in the (Of DataRow) part:

Dim X As New Entities
Dim query As IEnumerable(Of DataRow) = From cajero In X.CAJERO.AsEnumerable
                                       Select cajero
Dim bla = query.CopyToDataTable(Of DataRow)()

I'm using this question as a guide: Filling a DataSet or DataTable from a LINQ query result set

If i use

query.CopyToDataTable()  
'instead of the overload 
query.CopyToDataTable(Of DataRow)

it throws an invalidCastException.

I'm looking for an easy way to accomplish this task, and this seemed to be the easiest one, without too much code and having to implement any "shredder" methods or such, but if that's the only way, then please point me in the right direction.

This is the error that throws(I localized it to english, its a bit different in spanish):

Cannot convert object of type WhereSelectEnumerableIterator to object of type IEnumerable System.Data.DataRow

I have tried the following:

Declare an extension method that would create the datatable like this:

 _
Public Function myToDataTable(Of T)(source As IEnumerable(Of T)) As DataTable
    Dim properties As PropertyInfo() = GetType(T).GetProperties()

    Dim output As New DataTable()

    For Each prop In properties
        output.Columns.Add(prop.Name, prop.PropertyType)
    Next

    For Each item In source
        Dim row As DataRow = output.NewRow()

        For Each prop In properties
            row(prop.Name) = prop.GetValue(item, Nothing)
        Next

        output.Rows.Add(row)
    Next

    Return output
End Function

But it always throws an exception while adding the columns to the datatable:

DataSet does not support System.Nullable

I also changed the linq query to this:

Dim query = From cajero In X.CAJERO
           Select cajero

Dim bla = query.myToDataTable

Following Jon's suggestion I found this question:

.NET - Convert Generic Collection to DataTable

Which just gave me the last bit of code I needed:

output.Columns.Add(prop.Name, If(Nullable.GetUnderlyingType(prop.PropertyType), prop.PropertyType))

and

row(prop.Name) = If(prop.GetValue(item, Nothing), DBNull.Value)

@Jon Its an EdmEntityType imported from a database, just a table with a couple of fields. — Andrei Dvoynos, Oct 15 '12 at 15:31

score 2 · Accepted Answer · answered Oct 15 '12 at 15:37

2

I believe the problem is that you're trying to convert an IEnumerable<T> of an arbitrary entity type - whereas CopyToDataTable() always requires the input to be a sequence of some kind of DataRow.

Unless your entity type actually derives from DataRow, that's not going to work. You could potentially write a LINQ query which creates a DataRow from each instance, but I believe you'll have to write that code yourself.

I believe in the question you referenced, the OP already had a strongly typed DataSet - at least the answer suggested that's the case.

answered Oct 15 '12 at 15:37

Jon Skeet

1,421,763
867
9,128
9,194

Yes that's what I could assume from his use of order.Field("OrderDate"), is it too much to ask to provide a bit of code to get started on the LINQ Query which creates a DataRow from each instance? – Andrei Dvoynos Oct 15 '12 at 15:42
@AndreiD: Well what have you tried, and what's going wrong? (I haven't done much ADO.NET for ages, particularly raw DataTable values... if you've got a strongly typed data table, do you have strongly typed row types as well? If so, that may well be your answer...) – Jon Skeet Oct 15 '12 at 15:43
I edited the question showing the attempt at creating a DataTable from the resultset – Andrei Dvoynos Oct 15 '12 at 15:55
@AndreiD: Right, that looks like you'll need to remove nullability, and convert to DbNull.Value. – Jon Skeet Oct 15 '12 at 16:00

CopyToDataTable(Of DataRow) shows syntax error

1 Answers1