CODEWITHSUNDEEP
.netvb.netlinq
balaji
balaji

Reputation: 1304

Can't copy LINQ result into datatable

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:

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

Upvotes: 2

Views: 3707

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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

Upvotes: 3

Related Questions