Can't copy LINQ result into datatable

Question

Below is my LINQ query:

Dim JoinedResult = From t1 In temp.AsEnumerable() _
                   Group Join t2 In tbCity.AsEnumerable() _
                   On t1.Field(Of String)("City") Equals t2.Field(Of String)("city_name") _
                   Into RightTableResults = Group _
                   From t In RightTableResults.DefaultIfEmpty
                   Select New With 
                   {
                        .ID = t1.Item("ID"), 
                        .CID = If(t Is Nothing, 
                        1,  
                        t.Item("city_gid"))
                   }

How can I copy values from "JoinedResult" to a datatable?

Note:

I'm not getting JoinedResult.CopyToDataTable()
I don't want to use a loop

score 3 · Answer 1 · edited May 23 '17 at 12:31

3

CopyToDataTable is already there since .NET 3.5. But the problem is that you want to create a DataTable "from the scratch" from an anonymous type. That doesn't work.

CopyToDataTable is an extension for IEnumerable<DataRow> only. So you either have to select one DataRow from your joined DataTables:

Dim query =  From t1 In temp.AsEnumerable() _
             Group Join t2 In tbCity.AsEnumerable() _
             On t1.Field(Of String)("City") Equals t2.Field(Of String)("city_name") Into RightTableResults = Group 
             From t In RightTableResults.DefaultIfEmpty
             Select t1
Dim table = query.CopyToDataTable()

or use this ObjectShredder which uses reflection, hence is not the most efficient way(C# implementation).

Apart from this, why do you need the DataTable at all? You could also use the anonymous type as DataSource or create a custom class which you can initialize from the query result.

Edit: You can also create a custom DataTable with the columns you need, for example(from your comment):

Dim tbTemp As New DataTable
tbTemp.Columns.Add("ID")
tbTemp.Columns.Add("CID")

Dim query = From t1 In temp.AsEnumerable()
            Group Join t2 In tbCity.AsEnumerable() On
            t1.Field(Of String)("City") Equals t2.Field(Of String)("city_name")
            Into RightTableResults = Group
            From t In RightTableResults.DefaultIfEmpty
            Select New With {.ID = t1.Item("ID"), .City_Gid = t.Item("city_gid")}
For Each x In query
    tbTemp.Rows.Add(x.ID, x.City_Gid)
Next

edited May 23 '17 at 12:31

Community

1
1

answered Jan 29 '13 at 08:10

Tim Schmelter

450,073
74
686
939

i'm not getting "city_gid"(from t) if i use select t1. i need value t.Item("city_gid") also – balaji Jan 29 '13 at 08:21
@balaji: Why do you need the `DataTable`? Maybe it would be sufficient to use the `IEnumerable` in a `foreach` or you could create a custom class and a `List`. The `ObjectShredder` does also work for any type(even anonymous) but is more difficult and not the most efficient. – Tim Schmelter Jan 29 '13 at 08:23
bcz i need to add few more colums with datatable and need to insert the datatable into database(using bulkcopy) – balaji Jan 29 '13 at 08:31
@balaji: Then create a destination DataTable with all columns you need and add the rows in a `foreach` from the result of the query. – Tim Schmelter Jan 29 '13 at 08:40
i got solution but this may be silly or stupid Dim tbTemp As New DataTable tbTemp.Columns.Add("ID") tbTemp.Columns.Add("CID") Dim JoinedResult = From t1 In temp.AsEnumerable() _ Group Join t2 In tbCity.AsEnumerable() _ On t1.Field(Of String)("City") Equals t2.Field(Of String)("city_name") _ Into RightTableResults = Group _ From t In RightTableResults.DefaultIfEmpty Select tbTemp.LoadDataRow(New Object() {t1.Item("ID"),t.Item("city_gid")) datatable=JoinedResult.CopyToDataTable() – balaji Jan 29 '13 at 10:03
@balaji: Not exactly, i've edited my answer to show what i've meant. – Tim Schmelter Jan 29 '13 at 10:46
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/23561/discussion-between-balaji-and-tim-schmelter) – balaji Jan 29 '13 at 10:51

Can't copy LINQ result into datatable

1 Answers1

Linked