LINQ to DATASET with Group Join and Group By (vb.net)-CodePudding

I've got 2 datatables and am trying to summarize the data in them using a left outer join. The join works fine with this code

Dim Journal = From entries In dt.AsEnumerable()
              Join inccodes In dtGL.AsEnumerable()
              On entries.Field(Of String)("GLCode") Equals inccodes.Field(Of String)("GLCode")                                                                                                                
              Group By keys = New With {Key .IncomeCode = entries.Field(Of String)("GLCode"), Key .IncomeDesc = .inccodes.Field(Of String)("GLCodeDesc")}
              Into ChargeSum = Group, sm = Sum(entries.Field(Of Decimal)("Amount"))
              Where sm <> 0
              Select New GL_Journal With {.IncomeCode = keys.IncomeCode, .IncomeDesc = keys.IncomeDesc, .LineAmount = sm}

                                                      `

However, since I really want a Left Outer Join I want to use Group Join instead of Join. As soon as I change the Join to Group Join the code in the Group by at ".inccodes.field(Of String)("GLCodeDesc")" has ".inccodes" highlighted with the error "'inccodes' is not a member of 'anonymous type'" I've reviewed much documentation on Group By and Group Join but there is scant information on them together. Any ideas? Would I have more options/success with the method syntax?

CodePudding user response：

If i try to reproduce your query using a left outer join, I will do something like this :

Dim dt As New DataTable
dt.Columns.Add("GLCode", GetType(String))
dt.Columns.Add("Amount", GetType(Decimal))
dt.Rows.Add("111", 3251.21)
dt.Rows.Add("222", 125.79)
dt.Rows.Add("999", 10000)

Dim dtGL As New DataTable
dtGL.Columns.Add("GLCode", GetType(String))
dtGL.Columns.Add("GLCodeDesc", GetType(String))
dtGL.Rows.Add("111", "a")
dtGL.Rows.Add("222", "b")
dtGL.Rows.Add("333", "c")

Dim Journal = From entries In dt.AsEnumerable()
              Group Join inccodes In dtGL.AsEnumerable()
                On entries.Field(Of String)("GLCode") Equals inccodes.Field(Of String)("GLCode")
                Into Group
              From lj In Group.DefaultIfEmpty()              
              Group By keys = New With {Key .IncomeCode = entries.Field(Of String)("GLCode"), Key .IncomeDesc = lj?.Field(Of String)("GLCodeDesc")}
                Into ChargeSum = Group, sm = Sum(entries.Field(Of Decimal)("Amount"))                
              Select New With {.IncomeCode = keys.IncomeCode, .IncomeDesc = keys.IncomeDesc, .LineAmount = sm}