Join 2 Queries with a key

Buffie · August 2017


Dim dc As DataContext = Me

' Dim t = dc.GetTable(Of Cash)()

Dim QueryCash = From c In Cashs _
Group By Key = c.TeamId Into Group _
Select Teamid = Key, _
TotDay = Group.Sum(Function(c) c.cashwon),
GrandTot = Group.Sum(Function(c) c.AverageWon)

Dim tm = dc.GetTable(Of Teams) 

' Following Works
' QueryCash.ToList() 

QueryCash.Dump() 
tm.Dump()

The above code works now I would like to combine QueryCash and tm into a combined result on a key they both have.
First I am brand new to Linq, LinqPad5 and please note I use vb.net.
Any help would be appreciated. TIA

Buffie · August 2017

I got it to work. Of course, I have no idea whether there is a much better way or not. But it works for what I have to accomplish. If there is a much better way I would like some input.


Sub Main
Dim dc As DataContext = Me

' Dim t = dc.GetTable(Of Cash)()

Dim QueryCash = From c In Cashs _
Group By Key = c.TeamId Into Group _
Select Teamid = Key, _
TotDay = Group.Sum(Function(c) c.cashwon),
GrandTot = Group.Sum(Function(c) c.AverageWon)

Dim tm = dc.GetTable(Of Teams)

Dim res = From tc In querycash, tms In tm _
Where tc.Teamid = tms.teamsid _
Select tms.TeamName, tms.TeamSponsor, tms.LeadRider, tc.TotDay, tc.GrandTot, _
RealTot = (tc.TotDay + tc.GrandTot) Order By RealTot Descending

' Following Works
' QueryCash.ToList()
res.ToList

' QueryCash.Dump() 
' tm.Dump()
res.Dump()

'Dim dataContext As DataContext = Me
'
'Dim books = dataContext.GetTable(Of Book)()
'Dim query = From book In books _
' Group By key = book.Subject Into Group _
' Select id = key, _
' BookCount = Group.Count, _
' TotalPrice = Group.Sum(Function(_book) _book.Price), _
' LowPrice = Group.Min(Function(_book) _book.Price), _
' HighPrice = Group.Max(Function(_book) _book.Price), _
' AveragePrice = Group.Average(Function(_book) _book.Price)
'
'query.Dump()
End Sub

Buffie · August 2017

After many more iterations, I came up with the following which returns the same data as above. The top one executes in .236 seconds and this one in .165. So, over a super huge data set this one would save a great deal of time.


Dim res = From t In Teams _
Group Join c In Cashs On _
t.TeamsID Equals c.TeamId _
Into teamcash = Group, _
fintot = Sum(c.AverageWon), _
DayTot = Sum(c.CashWon) _
Select t.TeamName, t.LeadRider, _
t.TeamSponsor, _
DayTot, fintot, grantot = (DayTot + fintot) _
Order By grantot Descending

res.Dump()

Join 2 Queries with a key

Comments