Add subitems between groups with Linnus

Question

How can I perform calculations between subsets of a group by query in English? Follow example:

Consulta Linq

var listPlanoDeContas = (from t in _planoDeContasRepository.GetAll()
                         orderby t.Month ascending
                         group t by t.DfpChartOfAccounts into dfpGroup
                         select dfpGroup).ToList();

To illustrate, this query returns something like:

Plano de Contas      |    Mês 1    |    Mês 2     |    Mês 3    | ...

Receitas Totais           10000,00        13000,00      8000,00
Deduções                    878,00         1020,00       780,00
Custos                     1345,00         1478,00       850,00
Despesas                   3789,00         4342,00      1678,00
______________________________________________________________________
Saldo de Caixa                ?               ?             ?

I need to find the cash balance. Then I would like a help to calculate via Linq or Lambda the values between each item of the account plan per month. Ie:

Total Month Revenue 1 - Month Deductions 1 - Month Costs 1 - Month Expenses 1

Thanks in advance.

Answer 1

Note that in the example below I am using an example class and hypothetical fields of your class.

But I believe that only the code below meets your need, if your return on listPlanoDeContas for what you posted in the question.

SaldoCaixa =
new TotaisSaldoCaixa
{
    Total_Janeiro = listPlanoDeContas
        .Select(c => c.ReceitaJan - c.DeducoesJan - c.CustosJan - c.DespesasJan).SingleOrDefault(),

    Total_Fevereiro = listPlanoDeContas
        .Select(c => c.ReceitaFeb - c.DeducoesFeb - c.CustosFeb - c.DespesasFeb).SingleOrDefault(),

    Total_Marco = listPlanoDeContas
        .Select(c => c.ReceitaMar - c.DeducoesMar - c.CustosMar - c.DespesasMar).SingleOrDefault(),
    //Restante dos meses...
};

Answer 2

I decided as follows:

//Fiz uma consulta geral no DB e agrupei por mês
var qry = _planoDeContasRepository.GetAll()
	 .GroupBy(x => x.Month).ToList();

//Pecorre a consulta aplicando o filtro e realizando o cálculo
foreach(var i in qry)
{
    var valorReceita = i.Where(x => x.Categoria.Equals("Receita")).Select(x => x.Valor).ToList();
    decimal valor = Convert.ToDecimal(valorReceita[0]);
    
    var somaTotalPorMes = i.Sum(x => x.Valor);

    decimal saldo = Convert.ToDecimal(somaTotalPorMes) - valor;
}

//Retorno o saldo e....