VB.Net LINQ - 两个数据表之间的左外连接 - 限制为一行

Question

我对 vb.net 中的 LINQ 有疑问。基本上我想在两个数据表之间创建 LEFT JOIN。

这是两个数据表的信息:

Dim vDT1 As New DataTable
vDT1.Columns.Add("Key")
vDT1.Columns.Add("Data1")
vDT1.Columns.Add("Data2")

vDT1.Rows.Add({"01", "DATA1_AAAA", "DATA2_AAAA"})
vDT1.Rows.Add({"02", "DATA1_BBBB", "DATA2_BBBB"})

Dim vDT2 As New DataTable
vDT2.Columns.Add("Key")
vDT2.Columns.Add("Data3")
vDT2.Columns.Add("Data4")

vDT2.Rows.Add({"01", "DATA3_AAAA", "DATA4_AAAA"})
vDT2.Rows.Add({"01", "DATA3_BBBB", "DATA4_BBBB"})
vDT2.Rows.Add({"01", "DATA3_CCCC", "DATA4_CCCC"})
vDT2.Rows.Add({"01", "DATA3_DDDD", "DATA4_DDDD"})

Dim vDRnull As DataRow = vDTsec.Rows.Add

为了实现 LINQ JOIN 我尝试使用下一句话:

From a In vDT1.AsEnumerable
Group Join bTemp In vDT2.AsEnumerable
On a.Field(Of String)("Key") Equals bTemp.Field(Of String)("Key")
Into Group From b In Group.DefaultIfEmpty(vDRnull)
Select a, b

这是结果(但我想删除红色行)，我只想在第二个表中获得第一次出现:



任何帮助将不胜感激!

我用正确的方法编辑问题。感谢杰夫梅尔卡多:

Dim vDT1 As New DataTable
vDT1.Columns.Add("Key")
vDT1.Columns.Add("Data1")
vDT1.Columns.Add("Data2")

vDT1.Rows.Add({"01", "DATA1_AAAA", "DATA2_AAAA"})
vDT1.Rows.Add({"02", "DATA1_BBBB", "DATA2_BBBB"})

Dim vDT2 As New DataTable
vDT2.Columns.Add("Key")
vDT2.Columns.Add("Data3")
vDT2.Columns.Add("Data4")

vDT2.Rows.Add({"01", "DATA3_AAAA", "DATA4_AAAA"})
vDT2.Rows.Add({"01", "DATA3_BBBB", "DATA4_BBBB"})
vDT2.Rows.Add({"01", "DATA3_CCCC", "DATA4_CCCC"})
vDT2.Rows.Add({"01", "DATA3_DDDD", "DATA4_DDDD"})

Dim vDRnull As DataRow = vDT2.Rows.Add

Dim vLINQ = From a In vDT1.AsEnumerable
    Group Join bTemp In vDT2.AsEnumerable
    On a.Field(Of Object)("Key") Equals bTemp.Field(Of Object)("Key")
    Into Group Let b = If(Group.FirstOrDefault Is Nothing, vDRnull, Group.FirstOrDefault)
    Select a, b

Answer 1

关键是调用FirstOrDefault()在群里。然后你要么拥有组中的第一行，要么拥有 Nothing .

Dim query =
    From a In vDT1.AsEnumerable
    Group Join b In vDT2.AsEnumerable
        On a.Field(Of String)("Key") Equals b.Field(Of String)("Key")
        Into Group
    Let b = Group.FirstOrDefault
    Select Key = a.Field(Of String)("Key"),
           Data1 = a.Field(Of String)("Data1"),
           Data2 = a.Field(Of String)("Data2"),
           Data3 = If(b Is Nothing, Nothing, b.Field(Of String)("Data3")),
           Data4 = If(b Is Nothing, Nothing, b.Field(Of String)("Data4"))

    ' Or Simply
    'Select a, b