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