CODEWITHSUNDEEP
c#linqjoingroup-byaggregate
xexe1982
xexe1982

Reputation: 41

transform sql to linq with join, group-by and where

I have the following sql, which I want to convert to linq

SELECT Contrato.finca, SUM(Pago.Importe_subtotal)
FROM Pago, Contrato
WHERE Pago.Contrato = Contrato.ID AND Pago.pagado = 1
GROUP BY Contrato.finca
ORDER BY 2 DESC
GO

What I have now in linq is the following, but the group by doesn't work. 

var x = from contrato in ctx.Contratos 
        join pago in ctx.Pagos
        on contrato.ID equals pago.Contrato 
        where pago.pagado == true 
        group contrato by contrato.finca
        select contrato.Finca1;

Upvotes: 3

Views: 307

Answers (2)

Chris Woolum
Chris Woolum

Reputation: 2904

Think this should work:

ctx.Pagos.Where(m=>m.pagado==true).GroupBy(m=>m.Contrato.finca) 
    .Select(m=> new { Id= m.Key, Val= m.Sum(n => n.Importe_subtotal)})

Upvotes: 3

Vlad Bezden
Vlad Bezden

Reputation: 89527

Try

var x = from contrato in ctx.Contratos 
        join pago in ctx.Pagos
        on contrato.ID equals pago.Contrato 
        where pago.pagado == true 
        group new {contrato, pago} by contrato.finca into g
        select new {
            key = g.Key, 
            sum = g.Sum(p => p.pago.Importe_subtotal)
        };

Upvotes: 2

Related Questions