vb.net - LINQ 从 2 个数据表中选择所有列并作为 1 个数据表返回 VB.NET

Question

不幸的是，我把头发扯在这里了。

简而言之，我有一个DataTable要完成的包含数据，然后在一秒钟内DataTable我就有了操作的结果。（已完成、未完成等）

我需要将两组信息DataGridView连同一个LEFT OUTER JOIN.

这是我到目前为止所得到的：

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

当我尝试这样做时它失败了：

Select t1, t2

我本质上希望使用左外连接返回来自 t1 和 t2 的所有信息，因为 t2 中可能不存在针对 t1 中的所有值的任何“动作”记录。

我查看了 DataRelation，但是这不允许将所有数据返回到相同的DataGridView.

TLDR

想要从两个数据表中选择信息，使用左外连接将它们连接在一起，并将它们作为单个数据表返回以在 datagridview 中使用。

一样多

标签： vb.netlinq

Answer 1

使用一些扩展，您可以使用旨在将DataRows 的匿名对象中的结果合并到 new中的方法DataTable。我在这里写了一个答案，但这使用了我学到的一些新技术：

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

有了这个扩展，你可以只用join你DataTable的 s 然后转换答案：

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