CODEWITHSUNDEEP
asp.net.netvb.netlinqdatatable
Neeraj Kumar Gupta
Neeraj Kumar Gupta

Reputation: 2363

Join two datatable using LINQ and get result in other datatable

I have two datatables and I am joining them to select some matched records, I need some columns from both tatatable as a new result set which I am doing like this

        Dim query4 = From x In dtblPolicyFormStopCodes.AsEnumerable() Join y In dtblPolicyFormLetterReq.AsEnumerable()
        On x.Field(Of String)("Code") Equals y.Field(Of String)("Code") _
        Select New With _
        { _
            .Code = x.Field(Of String)("Code"), _
            .Sequence = x.Field(Of Integer)("Sequence "), _
            .FieldDataType= x.Field(Of String)("FieldDataType"), _
            .FieldValue= y.Field(Of String)("FieldValue") _
        }

        MyNewDataTable = GetDynamicTableSchema()

        query4.Cast(Of DataRow).CopyToDataTable(MyNewDataTable, LoadOption.OverwriteChanges)

but I am unable to get new datatable with selected columns of both datatables, please help me how can I get new result set in my "MyNewDataTable " or as a new datatable 

Public Function GetDynamicTableSchema() As System.Data.DataTable
    Dim tblDynamicTableDataMain As System.Data.DataTable = Nothing
    Try
        tblDynamicTableDataMain = New System.Data.DataTable("DynamicTableData")
        With tblDynamicTableDataMain
            .Columns.Add("Code")
            .Columns.Add("Sequence", System.Type.GetType("System.Int32"))
            .Columns.Add("FieldDataType")
            .Columns.Add("FieldValue")
        End With
        Return tblDynamicTableDataMain
    End Try
End Function

Upvotes: 0

Views: 1925

Answers (1)

Neeraj Kumar Gupta
Neeraj Kumar Gupta

Reputation: 2363

I achieved my desire functionality by using this way

Dim dr As DataRow
Dim MyNewDataTable as Datatable = GetDynamicTableSchema()

Dim query = (From x In dtblPolicyFormStopCodes.AsEnumerable() Join y In dtblPolicyFormLetterReq.AsEnumerable()
            On x.Field(Of String)("Code") Equals y.Field(Of String)("Code")) _
            .Select(Function(a) As DataRow
                        dr = MyNewDataTable.NewRow()
                        dr("Code") = a.x.Field(Of String)("Code")
                        dr("Sequence") = a.x.Field(Of Integer)("Sequence")
                        dr("FieldDataType") = a.x.Field(Of String)("FieldDataType")
                        dr("FieldValue") = a.y.Field(Of String)("Fieldvalue")
                        Return dr
                    End Function)

MyNewDataTable = query.CopyToDataTable()

Upvotes: 1

Related Questions