VB.Net LINQ - 两个数据表之间的左外连接 - 限制为一行 [英] VB.Net LINQ - left outer join between two datatables - limit to one row
问题描述
这是两个数据表的信息:
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,我尝试使用下一句:
从在vDT1.AsEnumerable
组加入bTemp在vDT2.AsEnumerable
在a.Field(Of S tring)(Key)等于bTemp.Field(Of String)(Key)
进入组从b在Group.DefaultIfEmpty(vDRnull)中
选择a,b
这是结果(但我想删除红色的行),我只想得到第二个表中的第一个出现: p>
任何帮助将不胜感激!
我用正确的方法编辑问题。感谢Jeff Mercado:
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。添加
Dim vLINQ =从a vDT1.AsEnumerable
组加入bTemp在vDT2.AsEnumerable
在a.Field(Of Object)(Key)等于bTemp.Field (对象)(Key)
进入组让b = If(Group.FirstOrDefault is nohing,vDRnull,Group.FirstOrDefault)
选择a,b
关键是在组上调用 FirstOrDefault()
。那么你要么是组中的第一行,要么是 Nothing
。
Dim query =
从a vDT1.AsEnumerable
组加入b在vDT2.AsEnumerable
在a.Field(Of String)(Key)等于b.Field(Of String)( key)
进入组
让b = Group.FirstOrDefault
选择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))
'或者简单的
'选择a,b
I have an issue with LINQ in vb.net. Basically I want create LEFT JOIN between two datatables.
This is the info of the two datatables:
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
In order to achieve the LINQ JOIN I try to use the next sentence:
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
This is the result (but I want remove the red rows), I only want get the first occurrence in the second table:
Any help will be appreciated!
I edit the question with the correct method. Thanks to Jeff Mercado:
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
The key is to call FirstOrDefault()
on the group. Then you either have the first row in the group or 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
这篇关于VB.Net LINQ - 两个数据表之间的左外连接 - 限制为一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!