Entity Framework Sum and Count different status

Question

In my ASP.NET MVC project, I’m trying to achieve the equivalent of this SQL Server code:

Select 
    e.Numero, 
    e.ValorEmprestimo, 
    Count(Case when pvp.IdeStatus = false and pvp.DatVencimento >= '20190710' then pvp.Id else null end) as NumParcelaAVencidaPendente,
    Sum(Case when pvp.IdeStatus = false and pvp.DatVencimento < '20190710' then pvp.ValParcela else 0 end) as TotParcelaVencidaPendente,
    Sum(Case when pvp.IdeStatus = false and pvp.DatVencimento >= '20190710' then pvp.ValParcela else 0 end) as TotParcelaAVencerPendente,
    Sum(Case when pvp.IdeStatus = true then pvp.ValParcela else 0 end) as TotParcelaPaga
From 
    Emprestimo e
    Inner Join Parcela pvp On (pvp.EmprestimoId = e.Id)
GROUP By e.Numero
Order by e.Id

Dice

    Empréstimo
    Id  Valor
    2   200000.0

    Parcela    
    DatVencimento ValParcela    EmprestimoId    IdeStatus
    05/07/2019    20165.37      2               1
    05/08/2019    20165.37      2               0
    05/09/2019    20165.37      2               0
    05/10/2019    20165.37      2               0
    05/11/2019    20165.37      2               0
    05/12/2019    20165.37      2               0
    05/01/2020    20165.37      2               0
    05/02/2020    20165.37      2               0
    05/03/2020    20165.37      2               0
    05/04/2020    20165.37      2               0

Upshot

Using the Entity Framework, in a single query, how to bring the lines with the loans and columns the sum values of the installments according to their status? It is possible to bring in a single call and get the same result or in this case should proceed using a precedent on the SQL side?

Answer 1

If the Loan and Installment tables are well related:

var data = new DateTime(2019, 7, 10);

var result = conn.Emprestimo
    .Select(e => new
    {
         e.Id,
         e.Numero,
         e.ValorEmprestimo,
         e.Parcela.IdeStatus,
         e.Parcela.ValParcela,
         e.Parcela.DatVencimento
    }
    .OrderBy(e => e.Id)
    .GroupBy(e => e.Numero, (num, list) => new
    {
         Numero = num,
         ValorEmprestimo = list.First(o => o.ValorEmprestimo),
         NumParcelaAVencidaPendente = list.Count(o => !o.IdeStatus && o.DatVencimento >= data),
         TotParcelaVencidaPendente = list.Sum(o => !o.IdeStatus && o.DatVencimento < data),
         TotParcelaAVencerPendente = list.Sum(o => !o.IdeStatus && o.DatVencimento >= data),
         TotParcelaPaga = list.Sum(o => o.IdeStatus)
    }
    .ToArray();