linq 如何left join （帶測試代碼）

[JirlangLianHongCh]

linq left join 測試代碼 有數據EXCEL表 謝謝大神幫忙解決下：
2個datatable結果集合併（LEFT JOIN）
excel2003測試表
SUVZHG：
http://pan.baidu.com/s/1i39Nkln
TOPRC：
http://pan.baidu.com/s/1gd417Xp

Function SUVZHG() As DataTable
Dim fileName As String
Dim kk As New OpenFileDialog()
kk.FileName = "SUVZHG"
kk.Filter = "Excle恅璃(*.xls)|*.xls"
If kk.ShowDialog() = DialogResult.OK Then
fileName = kk.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim dr As New DataTable
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(dr)
SUVZHG = dr
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End Function
Function TOPRC() As DataTable
Dim fileName As String
Dim kk As New OpenFileDialog()
kk.FileName = "TOPRC"
kk.Filter = "Excle恅璃(*.xls)|*.xls"
If kk.ShowDialog() = DialogResult.OK Then
fileName = kk.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim dr As New DataTable
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(dr)
TOPRC = dr
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End Function
Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
Dim q = From emp In TOPRC() Group Join emc In SUVZHG() On emp("JSX") Equals emc("JSX") Into DataGroup = Group
From row In DataGroup.DefaultIfEmpty()
Select emp, Order = row
DG1.DataSource = q.CopyToDataTable
End Sub

[q107770540]

给你个例子，你参考一下，我想你可以写的出来：

Public Sub Linq105()
    Dim categories() = {"Beverages", "Condiments", "Vegetables", "Dairy Products", "Seafood"}
 
    Dim productList = GetProductList()
 
    Dim query = From c In categories _
                Group Join p In productList On c Equals p.Category Into Group _
                From p In Group.DefaultIfEmpty() _
                Select Category = c, ProductName = If(p Is Nothing, "(No products)", p.ProductName)
 
    For Each v In query
        Console.WriteLine(v.ProductName + ": " + v.Category)
    Next
End Sub

http://msdn.microsoft.com/zh-cn/vstudio/bb737909#lojoin

[JirlangLianHongCh]

版主
我玩了一天沒玩出來
你提供的例子我也參考過，其中
p.Category 是p("Category")意思？我用的是VB.net 2010

join linq
Dim TOPE = From cnk In Mycj.AsEnumerable() _
Join bnk In MyCon.AsEnumerable() _
On cnk("pw") Equals bnk("brand") _
Select New With {.dl = cnk("dl"), _
.pp = cnk("pp"), _
.pw = cnk("pw"), _
.bs = cnk("bs"), _
.thbs = cnk("thbs"), _
.cjje = cnk("cjje"), _
.mc = cnk("mc"), _
.歸類品牌 = cnk("歸類品牌"), _
.floor = bnk("floor"), _
.sitegrade = bnk("sitegrade"), _
.JSX = bnk("floor") & bnk("sitegrade") & cnk("pp") & cnk("dl")}
Dim TOPR As New DataTable
TOPR.Columns.Add("pp")
TOPR.Columns.Add("pw")
TOPR.Columns.Add("bs")
TOPR.Columns.Add("thbs")
TOPR.Columns.Add("cjje")
TOPR.Columns.Add("mc")
TOPR.Columns.Add("GLPP")
TOPR.Columns.Add("floor")
TOPR.Columns.Add("WZ")
TOPR.Columns.Add("JSX")
For Each Mytables In TOPE
Dim MyRows = TOPR.NewRow()
MyRows("pp") = Mytables.pp
MyRows("pw") = Mytables.pw
MyRows("bs") = Mytables.bs
MyRows("thbs") = Mytables.thbs
MyRows("cjje") = Mytables.cjje
MyRows("mc") = Mytables.mc
MyRows("GLPP") = Mytables.歸類品牌
MyRows("floor") = Mytables.floor
MyRows("WZ") = Mytables.sitegrade
MyRows("JSX") = Mytables.JSX
TOPR.Rows.Add(MyRows)
Next
上面代碼測試通過
若直接

From p In Group.DefaultIfEmpty() select p

[JirlangLianHongCh]

引用 1 楼 q107770540 的回复:

给你个例子，你参考一下，我想你可以写的出来：

Public Sub Linq105()
    Dim categories() = {"Beverages", "Condiments", "Vegetables", "Dairy Products", "Seafood"}
 
    Dim productList = GetProductList()
 
    Dim query = From c In categories _
                Group Join p In productList On c Equals p.Category Into Group _
                From p In Group.DefaultIfEmpty() _
                Select Category = c, ProductName = If(p Is Nothing, "(No products)", p.ProductName)
 
    For Each v In query
        Console.WriteLine(v.ProductName + ": " + v.Category)
    Next
End Sub

http://msdn.microsoft.com/zh-cn/vstudio/bb737909#lojoin

vb.net left join分享是美德,搞了2天,目次成功了上图

Function SUVZHG() As DataTable
Dim fileName As String
Dim kk As New OpenFileDialog()
kk.FileName = "SUVZHG"
kk.Filter = "Excle文件(*.xls)|*.xls"
If kk.ShowDialog() = DialogResult.OK Then
fileName = kk.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim dr As New DataTable
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(dr)
SUVZHG = dr
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End Function
Function TOPRC() As DataTable
Dim fileName As String
Dim kk As New OpenFileDialog()
kk.FileName = "TOPRC"
kk.Filter = "Excle文件(*.xls)|*.xls"
If kk.ShowDialog() = DialogResult.OK Then
fileName = kk.FileName
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
Dim dr As New DataTable
Dim da As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
Try
da.Fill(dr)
TOPRC = dr
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim vDRnull As DataRow = SUVZHG.Rows.Add

Dim vLINQ = From a In TOPRC.AsEnumerable
Group Join bTemp In SUVZHG.AsEnumerable
On a.Field(Of Object)("JSX") Equals bTemp.Field(Of Object)("JSX")
Into Group Let b = If(Group.FirstOrDefault Is Nothing, vDRnull, Group.FirstOrDefault)
Select New With {.JSX = a("JSX"), .TT = b("bcsm")}
DataGridView1.DataSource = vLINQ.ToArray
End Sub

[hzybc]

Dim productList = GetProductList() 这句是什么意思？ 有错误，是不是还有其它条件