c# - Sum(with Coalesce) with Group By in linq (for EF6)

Question

我有以下 SQL，我想将其编写为单个 linq 语句:

SELECT
     P.PartyId,
     P.PartyDate,
     SUM(COALESCE(R.PaidAmount, 0)) AS AmountPaid
FROM 
     Party AS P 
     LEFT JOIN Reservation as R
          ON P.PartyID = R.PartyID 
GROUP BY P.PartyID, P.PartyDate 
ORDER BY P.PartyDate DESC

我能做的最好的就是使用两组 linq 查询，如下所示:

var localList = from partyList in localDb.Parties
                join reservationList in localDb.Reservations on
                     partyList.PartyID equals reservationList.PartyID into comboList
                from newList in comboList.DefaultIfEmpty()
                select new PartyAmounts {
                                PartyID = partyList.PartyID,
                                PartyDate = partyList.PartyDate,
                                AmountPaid = (newList.PaidAmount ?? 0) };

var secondList = from groupList in localList
                 group groupList by new {
                                       groupList.PartyID,
                                       groupList.PartyDate} into resList
                 select new PartyAmounts {
                                 PartyID = resList.Key.PartyID,
                                 PartyDate=resList.Key.PartyDate,
                                 AmountPaid = resList.Sum(x => x.AmountPaid)};

我不在乎它是方法链还是 lambda，但我很想知道这应该如何组合在一起。我现在只能勉强理解我的两个。

感谢您的帮助。

Answer 1

var list = from partyList in localDb.Parties
           join reservationList in localDb.Reservations on partyList.PartyID equals reservationList.PartyID into comboList
           from details in comboList.DefaultIfEmpty() // Left join
           group details by new {partyList.PartyID, partyList.PartyDate} into grouped // So that the group have both keys and all items in details
           select new PartyAmounts
           {
             PartyID = grouped.Key.PartyID,
             PartyDate = grouped.Key.PartyDate,
             AmountPaid = grouped.Sum(x => x.AmountPaid ?? 0)}
           };