Join 2 Queries with a key

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

Comments

  • 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
  • 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()
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!