CODEWITHSUNDEEP
c#linqlambda
Chris F.
Chris F.

Reputation: 3

C# Linq nested GroupBy and Sum

I'm trying to translate a query I've written to Linq for the past few days I can't seem to make it work. This is the query I'm trying to translate:

SELECT
    hsd.CoveragePeriodBeginDate,
    RateTotal           = SUM(hsd.Rate),
    ReimbursementTotal  = SUM(hsd.TotalReimbursement),
    AdjustmentsTotal    = SUM(hsd.Adjustments)
FROM
    (   SELECT 
            CoveragePeriodBeginDate,
            PaidDate,
            Rate                = TotalClaimCharge, 
            TotalReimbursement  = ReimbursementAmount,
            Adjustments         = SUM(BaseRateChangeAmount)
        FROM
            dbo.HsdMonthlyCapitatation
        WHERE
            MemberID = 12345678
        GROUP BY
            CoveragePeriodBeginDate,
            PaidDate,
            TotalClaimCharge,
            ReimbursementAmount
    )   hsd 
GROUP BY
    hsd.CoveragePeriodBeginDate
ORDER BY 
    hsd.CoveragePeriodBeginDate

What I need to do is translate this into Linq. I have tried many different ways, but can't seem to make it work right. It always seems to aggregate too much.

Here's the closest I've come. 

var rawCapData = db.HsdMonthlyCapitations.Where(x => x.MemberID == memberID)
    .Select(x => new {
                        CoveragePeriod = x.CoveragePeriodBeginDate,
                        TotalCharge = x.TotalClaimCharge,
                        Reimbursement = x.ReimbursementAmount,
                        PaidDate = x.PaidDate,
                        Adjust = x.BaseRateChangeAmount
                    })
    .GroupBy(x => new {
                        CoverageDate = x.CoveragePeriod,
                        Paid = x.PaidDate,
                        Rate = x.TotalCharge,
                        Reimburse = x.Reimbursement
                      })
    .GroupBy(x => new {
                        Coverage = x.Key.CoverageDate,
                        DhsRate = x.Sum(y => y.TotalCharge),
                        ReimbursementTotal = x.Sum(y => y.Reimbursement),
                        Adjustments = x.Sum(y => y.Adjust)
                      })
    .Select(x => new {
                        CapMonthYear = x.Key.Coverage,
                        DhsRate = x.Key.DhsRate,
                        TotalReimbursement = x.Key.ReimbursementTotal,
                        AdjustmentsTotal = x.Key.Adjustments
                     });

I should say I have gotten it to work, but I feel it's rather cludgey and a mix of regular LINQ and lambda expressions, and I would prefer to code it all with lambda expressions, if at all possible. Here's the code I have gotten to work: 

var rawCapitationData = from capitation
                        in db.HsdMonthlyCapitations
                        where capitation.MemberID == memberID
                        group capitation by new 
                             { 
                                capitation.CoveragePeriodBeginDate,
                                capitation.TotalClaimCharge,
                                capitation.ReimbursementAmount,
                                capitation.PaidDate
                             } into cap
                        select new {
                                      CapitationMonthYear = cap.Key.CoveragePeriodBeginDate,
                                      TotalReimbursement = cap.Key.TotalClaimCharge,
                                      DhsCapitationAmount = cap.Key.ReimbursementAmount,
                                      PaidDate = cap.Key.PaidDate,
                                      DhsAdjustments = cap.Sum(x => x.BaseRateChangeAmount)
                                   };

var capitationData = rawCapitationData.GroupBy(cap => cap.CapitationMonthYear)
      .Select(data => new {
                           CapitationDate = data.Key,
                           TotalReimbursement = data.Sum(x => x.TotalReimbursement),
                           DhsCapitationAmount = data.Sum(x => x.DhsCapitationAmount),
                           DhsAdjustments = data.Sum(x => x.DhsAdjustments)
                          });

My preference is to do this all in one statement. Is it even possible? I feel I'm close with the lambda expressions, but I know I'm missing something.

Any help or advice is greatly appreciated.

Upvotes: 0

Views: 1181

Answers (1)

Szer
Szer

Reputation: 3476

Not sure what are you trying to achieve, but I've ended up with this:

return db.HsdMonthlyCapitations
    .Where(x => x.MemberID == memberID)
    .GroupBy(x => new {x.CoveragePeriodBeginDate, x.PaidDate, x.TotalClaimCharge, x.ReimbursementAmount})
    .Select(x => new
    {
        x.Key.CoveragePeriodBeginDate,
        x.Key.PaidDate,
        Rate = x.Key.TotalClaimCharge,
        TotalReimbursement = x.Key.ReimbursementAmount,
        Adjustments = x.Sum(m => m.BaseRateChangeAmount)
    })
    .GroupBy(x => x.CoveragePeriodBeginDate)
    .Select(x => new
    {
        CoveragePeriodBeginDate = x.Key,
        RateTotal = x.Sum(m => m.Rate),
        ReimbursementTotal = x.Sum(m => m.TotalReimbursement),
        AdjustmentsTotal = x.Sum(m => m.Adjustments),
    })
    .OrderBy(x => x.CoveragePeriodBeginDate);

Upvotes: 1

Related Questions