Aggregate and create results list using EF-CodePudding

I'm having a list of budget units each one containing the following properties:

DateTime Month,
int IdCurrency,
decimal Planned,
int sign, //denotes whether we have income (1) or cost (0)
etc...

Based on given year, I'd like to return a list of objects of the following structure:

public class BudgetBalances
{
    public DateTime Month { get; set; }
    public int IdCurrency { get; set; }
    public decimal Incomes { get; set; }
    public decimal Costs { get; set; }
    public decimal Balance { get; set; }
}

The first part is easy - I'm getting all budget units for given day from the database, but now I do not know how to make an EF query to:

Get all incomes (sign==1) in currencies within one month, sum them and store it Incomes property
Get all costs (sign==0) and do the same as above
Substract Cost from Income and store it under Balance property

As the result I will have

Jan2022, USD, 3000, 1000, 2000 Jan2022, EUR, 5000, 2000, 3000

etc..

I can always make three level foreach structure, but that is not an effective way to do so. Could you please give me hint how to do it proper way?

That is what I got so far:

public List<BudgetBalances>GetYearlyBudget(int IdOwner, int year)
        {

            var budgets = _context.Budgets
                .Where(_ => _.Month.Year == year && _.IdOwner == IdOwner);

            List<BudgetBalances> list = budgets.GroupBy(a => a.Month)
                .Select(ls => new BudgetBalances
                {
    Incomes = ls.Where(_ => _.IsIncome == 1).Sum(_ => _.Planned),
                Costs = ls.Where(_ => _.IsIncome == 0).Sum(_ => _.Planned) 

                }).ToList();
            return list;   
        }

And it calculates each month budget taking into account incomes and costs, but it does not take currencies into consideration. Also I do not know how should I obtain balance value.

Balance = Income - Costs

does not work

CodePudding user response：

Reference this

code sample

using (var context = new MyContext())
{
    var result = context.BudgetBalances
                    .Where(b => b.IdCurrency == 1);
}

CodePudding user response：

Thanks, finally I got what I wanted, here's my code:

public List<BudgetBalances>GetYearlyBudget(int IdOwner, int year)
    {
        var budgets = _context.Budgets
            .Where(_ => _.Month.Year == year && _.IdOwner == IdOwner);

        List<BudgetBalances> list = budgets.GroupBy(a => new { a.Month, a.IdCurrency})
            .Select(ls => new BudgetBalances
            {
                IdCurrency = ls.Key.IdCurrency,
                CurrencySymbol = _context.Currencies.Where(_=>_.IdCurrency==ls.Key.IdCurrency).FirstOrDefault().CurrencySymbol,
                Month = ls.Key.Month,
                Incomes = ls.Where(_ => _.IsIncome == 1).Sum(_ => _.Planned),
                Costs = ls.Where(_ => _.IsIncome == 0).Sum(_ => _.Planned),
            })
            .OrderBy(_=>_.Month)
            .ToList();

        foreach(BudgetBalances ls in list)
        {
            ls.Balance = ls.Incomes - ls.Costs;
            ls.month = ls.Month.ToString("MM/yyyy");
        }
        
        return list;
    }