LINQ Select All Columns From 2 DataTables and Return as 1 DataTable VB.NET

Question

Tearing my hair out here unfortunately.

In a nut shell I have a DataTable containing data to be completed and then in a second DataTable I have the results of the actions. (completed, not completed etc.)

I need to return both sets of information into DataGridView together with essentially a LEFT OUTER JOIN.

This is what I've got so far:

Dim Query = From t1 In MasterTbl Group Join t2 In MasterActionTbl On t1.Field(Of String)("FreshAppsID") Equals t2.Field(Of String)("FreshAppsID") Into ps = Group From p In ps.DefaultIfEmpty()
                Select t1

    Return Query.CopyToDataTable

It fails when I attempt to do:

Select t1, t2

I essentially wish to return all the information from t1 and t2 using a left outer join because there may not be any 'action' records in existence in t2 for all the values in t1.

I looked into DataRelation's however this doesn't allow all the data to be returned into the same DataGridView.

TLDR

Want to select information from two datatables, join them together using a left outer join and return them as a single datatable for use in a datagridview.

Muchas

You're looking for a magic solution that doesn't exist. That `CopyToDataTable` method can only be used on a list of `DataRow` objects. It's basically a way to create a filtered copy of another `DataTable`. What you need to do is explicitly create a new `DataTable` with the columns you need, then loop through the result of your LINQ query. Inside the loop you call `NewRow`, populate the row and then call `Rows.Add`. — jmcilhinney, Sep 14 '18 at 09:52
You could write your own extension method that does what I suggested internally and then call that on your query result. You can probably find a number of such methods on the wen and, in fact, I posted one just a few days ago. The thing is, such methods with usually use Reflection on an `IEnumerable(Of T)` to determine the columns based on the properties of `T`. That would require you to change your `Select` clause to individually select each value rather than selecting the two `DataRows` whole. — jmcilhinney, Sep 14 '18 at 09:56
@jmcilhinney It returns all perfectly fine when I do "Select t1" surely if the join is working then the t2 should be on the same DataRow as the joined t1's a return all the same. They are just a Datarow. — Lynchie, Sep 14 '18 at 09:56
I specifically stated that `CopyToDataTable` works on and ONLY on an `IEnumerable(Of DataRow)`. If you use `Select t1` then, given that `t1` is type `DataRow`, the result will be `IEnumerable(Of DataRow)`. Of course that works. If you use `Select t1, t2` though, then you are going to return an `IEnumerable(Of T)` where `T` is NOT `DataRow`. It is something that contains two `DataRow` objects so obviously that's not a `DataRow`. I expect that it is an anonymous type with two properties of type `DataRow`, which is not `DataRow`. — jmcilhinney, Sep 14 '18 at 10:00
You can find the `ToDataTable` method that I posted recently [here](https://stackoverflow.com/questions/52194663/how-to-convert-linq-result-to-datatable-using-datatable-not-context). Asa I said though, you'd have to change your `Select` clause to select each desired field individually, e.g. `Select New With {.Name = t1.Field(Of String)("Name"), .Number = t2.Field(Of Integer)("Number")}`. — jmcilhinney, Sep 14 '18 at 11:30
@jmcilhinney Muchas - I'll give it a whirl, appreciate you're time. — Lynchie, Sep 14 '18 at 11:36

score 0 · Answer 1 · answered Sep 14 '18 at 22:08

Using some extensions, you can use a method designed to merge the results in an anonymous object of DataRows into a new DataTable. I wrote one for an answer here but this uses some new techniques I have picked up:

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

    ' Create new DataTable from LINQ results on DataTable
    ' Expect T to be anonymous object of form new { DataRow d1, DataRow d2, ... }
    <System.Runtime.CompilerServices.Extension()>
    Public Function FlattenToDataTable(Of T)(src As IEnumerable(Of T)) As DataTable
        Dim res = New DataTable()
        If src.Any Then
            Dim firstRow = src.First()
            Dim rowType = firstRow.GetType()
            Dim memberInfos = rowType.GetProperties.Cast(Of MemberInfo).Concat(rowType.GetFields).ToList
            Dim allDC = memberInfos.SelectMany(Function(mi) mi.GetValue(Of DataRow)(firstRow).Table.DataColumns())

            For Each dc In allDC
                Dim newColumnName = dc.ColumnName
                If res.ColumnNames.Contains(newColumnName) Then
                    Dim suffixNumber = 1
                    While (res.ColumnNames.Contains($"{newColumnName}.{suffixNumber}"))
                        suffixNumber += 1
                    End While
                    newColumnName = $"{newColumnName}.{suffixNumber}"
                End If
                res.Columns.Add(New DataColumn(newColumnName, dc.DataType))
            Next

            For Each objRows In src
                res.Rows.Add(memberInfos.SelectMany(Function(mi) mi.GetValue(Of DataRow)(objRows).ItemArray).ToArray())
            Next
        End If
        Return res
    End Function

    ' ***
    ' *** DataTable Extensions
    ' ***
    <System.Runtime.CompilerServices.Extension()>
    Public Function DataColumns(ByVal aTable As DataTable) As IEnumerable(Of DataColumn)
        Return aTable.Columns.Cast(Of DataColumn)
    End Function
    <System.Runtime.CompilerServices.Extension()>
    Public Function ColumnNames(ByVal aTable As DataTable) As IEnumerable(Of String)
        Return aTable.DataColumns.Select(Function(dc) dc.ColumnName)
    End Function

    ' ***
    ' *** MemberInfo Extensions
    ' ***
    <System.Runtime.CompilerServices.Extension()>
    Public Function GetValue(ByVal member As MemberInfo, srcObject As Object) As Object
        If TypeOf member Is FieldInfo Then
            Return DirectCast(member, FieldInfo).GetValue(srcObject)
        ElseIf TypeOf member Is PropertyInfo Then
            Return DirectCast(member, PropertyInfo).GetValue(srcObject)
        Else
            Throw New ArgumentException($"MemberInfo must be of type FieldInfo or PropertyInfo {Nameof(member)} but is of type {member.GetType}")
        End If
    End Function
    <System.Runtime.CompilerServices.Extension()>
    Public Function GetValue(Of T)(ByVal member As MemberInfo, srcObject As Object) As T
        Return DirectCast(member.GetValue(srcObject), T)
    End Function

End Module

With this extension, you can just join your DataTables and then convert the answer:

Dim Query = From t1 In MasterTbl
            Group Join t2 In MasterActionTbl On t1.Field(Of String)("FreshAppsID") Equals t2.Field(Of String)("FreshAppsID") Into ps = Group _
            From p In ps.DefaultIfEmpty()
            Select New With { t1, t2 }

Return Query.FlattenToDataTable

LINQ Select All Columns From 2 DataTables and Return as 1 DataTable VB.NET

1 Answers1