Linq join select all columns and CopyToDataTable

Question

TABLE SCHEMA I'm having trouble solving a query in linq to dataset; I have to make a simple left join between two datatable but I do not know in advance the exact number of columns in the datatable B (A, B, C ...?) so I wanted to select all the columns; I found the following link

select-all-columns-after-join-in-linq

select-all-columns-for-all-tables-in-join-linq-join

        Dim Query = From A In TableA _
                    Join B In TableB _
                    On A("COD") Equals B("COD") _
                    Select New With {A, B}
        Dim TableC As DataTable = Query.CopyToDataTable()

I tried also

Select New With {.DAT = A.ItemArray.Concat(P.ItemArray).ToArray()}).ToList

and many more but i failed to bring the query result to a new datatable; I received type conversion errors or i did not understand how to bring the query result provided in two separate tables into one datatable.

What do you expect to happen in the new `datatable` to the two columns `A("COD")` and `B("COD")`? — NetMage, Oct 04 '17 at 21:02
nothing, is just for not put in the [Select New With {.....] statement the individual columns to be selected from Table B because i can not determine how many columns are present in table B and i would not use dynamics linq (So I decided to select all the columns indistinctly) see TABLE SCHEMA — user8719632, Oct 04 '17 at 21:14
So your TABLE SCHEMA shows column `COD` twice, but that isn't allowed in a `DataTable`. — NetMage, Oct 04 '17 at 21:37
true, there is an easy way to avoid repeating the COD column and still have all the remaining columns in Table B tied together without even knowing the name of the column in advance?. On Table B I indicated A B C but it could be L M N O or any other sequence — user8719632, Oct 04 '17 at 21:56

NetMage · Answer 1 · 2017-10-04T22:26:08.570

Creating some extension methods and using Reflection will do what you want, though this may not be the ideal way to solve your issue (generally, Reflection never is).

Public Module SomeExt
<System.Runtime.CompilerServices.Extension()>
Public Function GetTypedValue(Of TProp)(ByVal p As PropertyInfo, obj As Object) As TProp
    Return DirectCast(p.GetValue(obj), TProp)
End Function

<System.Runtime.CompilerServices.Extension()>
Public Function FlattenToDataTable(Of T)(src As IEnumerable(Of T)) As DataTable
    Dim ans = New DataTable()

    Dim srcdtpis = GetType(T).GetProperties().Cast(Of PropertyInfo)().ToList()
    For Each aDT As DataTable In srcdtpis.Select(Function(pi) pi.GetTypedValue(Of DataRow)(src.First()).Table)
        For Each col In aDT.Columns.Cast(Of DataColumn)()
            ans.Columns.Add(col.ColumnName, col.DataType)
        Next
    Next

    For Each drs In src
        Dim newDR = ans.NewRow
        For Each aDR In srcdtpis.Select(Function(pi) pi.GetTypedValue(Of DataRow)(drs))
            For Each colname In aDR.Table.Columns.Cast(Of DataColumn)().Select(Function(dc) dc.ColumnName)
                newDR(colname) = aDR(colname)
            Next
        Next
        ans.Rows.Add(newDR)
    Next

    Return ans
End Function
End Module

You would use it as follows:

Dim Query = From A In TableA.AsEnumerable() _
                Join B In TableB.AsEnumerable() _
                On A("COD") Equals B("COD") _
                Select New With {A, B}
Dim TableC As DataTable = Query.FlattenToDataTable()

Note that duplicate column names will be set to the last DataTable's value.

great; when you've also entered how to use the function was the end of a nightmare; the only thing is that throw an error when it try to create the COD column twice; I solved changing the line as follow [If Not (ans.Columns.Contains(col.ColumnName)) Then ans.Columns.Add(col.ColumnName, col.DataType)] . thanks — user8719632, Oct 05 '17 at 07:09
I didn't think of the `Add` causing an issue - I modified the datarow assignments so it would just overwrite duplicates. — NetMage, Oct 05 '17 at 21:36

Linq join select all columns and CopyToDataTable

1 Answers1

Linked