Linq Sum for Multiple Joins

Question

Hi,

Can someone help me out with the following Linq statement? I'm trying to get join 4 tables through Linq, group by the last table and sum a property of the first.

var payments = (
                from payment in db.Payments
                join payees in db.cmsMembers on payment.PayeeID equals payees.nodeId
                join payeeGroups in db.cmsMember2MemberGroups on payees.nodeId equals payeeGroups.Member
                join groups in db.umbracoNodes on payeeGroups.MemberGroup equals groups.id
                group groups by new {groups.text, payment} into groupedPayments
                select new
                {
                    Group = groupedPayments.Key.text,
                    Count = groupedPayments.Count(),
                    Amount = groupedPayments.Sum(?)
                }
            ).ToList();

The issue I'm having is that the in the groupedPayments.Sum(..) call I'm only getting access to the "groups" object. This is fine for the Group name and count aspect but the SUM has to be performed on the payment.Amount property.

Answer 1

Try this:

Amount = (select amount from groupedPayments).Sum()

Answer 2

I've managed to resolve my issue. I hadn't realised that the object in the group declaration would become the actual object witin the resulting group item. By altering the original statement to the following:

group payment by groups into groupedPayments

it seems that each instance of groupedPayments.Key in the select statement now corresponds to the payment row which is what I required.