Linq to SQL: Group By and Sum()

Question

I'm very new to linq so this should be pretty easy to answer, but I've had a hard time finding the answer.

I have the following LINQ statement, which performs a simple linq query and assigns the resulting values labels on an asp.net web form:

        Dim db As New MeetingManagerDataContext
    Dim q = From s In db.vwRoomAvailabilities _
                Where s.MeetingID = lblMeetingID.Text _
                Select s.AllRequestedSingles, s.AllRequestedDoubles, s.AllBookedSingles, s.AllBookedDoubles, SinglesNeeded = s.AllRequestedSingles - s.AllBookedDoubles, DoublesNeeded = s.AllRequestedDoubles - s.AllBookedDoubles

    lblSinglesRequested.Text = "Singles Requested: " & q.FirstOrDefault.AllRequestedSingles
    lblSinglesBooked.Text = "Singles Booked: " & q.FirstOrDefault().AllBookedSingles
    lblSinglesNeeded.Text = "Singles Needed: " & q.FirstOrDefault().SinglesNeeded
    lblDoublesRequested.Text = "Doubles Requested: " & q.FirstOrDefault().AllRequestedDoubles
    lblDoublesBooked.Text = "Doubles Booked: " & q.FirstOrDefault().AllBookedDoubles
    lblDoublesNeeded.Text = "Doubles Needed: " & q.FirstOrDefault().DoublesNeeded

Originally, there was going to be only a single row result and you can see I'm using FirstOrDefault() to grab that single value which works great. But the design has changed, and multiple rows can now be returned by the query. I need to now Group By the MeetingID above, and SUM each of the selected columns (i.e. s.AllRequestedDoubles).

I've found lots of grouping and summing samples but none seem to fit this scenario very well.

Can you help me modify the above LINQ to Sum the resulting values instead of just showing the first row result values?

Answer 1

Try this

From s In db.vwRoomAvailabilities
Where s.MeetingID = lblMeetingID.Text
Group by s.MeetingID 
into SumAllRequestedDoubles = sum(s.AllRequestedDoubles),
     SumAggregate2 = sum(s.SomeField2),
     SumAggregate3 = sum(s.SomeField3)
Select SumAllRequestedDoubles, SumAggregate2, SumAggregate3

That will get you started for performing a SUM on that single column.

You'll need to project each SUM'd column into a new aliased column (like i did above).

Also, as you're new to LINQ-SQL, check out LinqPad - it will rock your world.