EF Core group by date and count value for previous dates-CodePudding

I have a table like

Date        Balance A   Balance B
2022.03.01  100         200
2022.03.01  200         500
2022.03.02  300         600
2022.03.02  50          100
2022.03.03  150         300
2022.03.03  200         700
2022.03.04  450         500
2022.03.04  600         100

And I need to group by Date and get total balance for the date and also for previous dates. I can do it like:

var groupedData = from d in _myContext.Balances 
                    group d by d.Date into dg
                    select new 
                    { 
                    Date = dg.Key, 
                    TotalBalanceA = dg.Sum(t=>t.BalanceA),
                    TotalBalanceB = dg.Sum(t=>t.BalanceB),
                    PreviousTotalA = ?,
                    PreviousTotalB = ?
                    }

But I'm not sure how to get total for previous dates.

So, if we take the table above, we should get grouped data like:

Date        TotalBalanceA   TotalBalanceB   PreviousTotalA  PreviousTotalB
2022.03.01  300             700             0               0
2022.03.02  350             700             300             700
2022.03.03  350             1000            650             1400
2022.03.04  1050            700             1000            2400

Update: I want to get these data from db. It is Iquerable

CodePudding user response：

Can you use subqueries?

var groupedData = from d in _myContext.Balances 
                    group d by d.Date into dg
                    select new 
                    { 
                        Date = dg.Key, 
                        TotalBalanceA = dg.Sum(t=>t.BalanceA),
                        TotalBalanceB = dg.Sum(t=>t.BalanceB),
                        PreviousTotalA = _myContext.Balances.Where(b => b.Date <= dg.Key).Sum(b => b.BalanceA),
                        PreviousTotalB = _myContext.Balances.Where(b => b.Date <= dg.Key).Sum(b => b.BalanceB)
                    }