Convert Left Join SQL to Linq C #

4

How can I convert the following SQL query to Linq?

SELECT Facturas.IDFactura, SUM(MovimientosDineros.Valor) AS Totalaportado
FROM Facturas
JOIN MovimientosDineros ON Facturas.IDFactura = MovimientosDineros.IdFactura
GROUP BY Facturas.IDFactura

I tried to do it like that but I can not finish it:

var consulta = (from fac in db.Facturas
                join mov in db.MovimientosDineros on fac.IDFactura equals mov.IdFactura);

Note: it is important that the query show all the invoices, and make the sum in the MovimientosDineros table. That's why in SQL I used Left Join .

    
asked by Jhon Castrillon 06.06.2017 в 06:14
source

4 answers

1

It is not very clear why you want to do a left join. I have assumed that it is because there are invoices that simply do not have assigned movements, so the following fragment of code revolves around that.

var q = from fac in db.Facturas
        join mov in db.MovimientosDineros
            on fac.IDFactura equals mov.IdFactura into facturasMovimientos // 1
        from facMov in facturasMovimientos.DefaultIfEmpty() // 2
        group facMov by fac into grupo // 3
        let sum = grupo.Sum(g => g == null ? 0 : g.Valor) // 4
        select new 
        {
            IDFactura = grupo.key.IDFactura,
            Totalaportado = sum
        };

I will explain the code using the numbers I have indicated in the comments:

  • In 1 what has been done is to join the invoices with their movements and has "englobado" this union in a table invoices Movements.
  • In 2 we specify that if an invoice has no movements, a default value (null) is returned. Consult the documentation of this function if you see it necessary.
  

Summary: Returns the elements of the specified sequence or the   default value of the type parameter in a singleton collection if the > sequence is empty.

  • In 3 we group all rows of invoices Movements by invoices.
  • In 4 the total sum is calculated specifying that if any row is null the summand is 0.

Explained the code, I have to say that I have not been able to prove it, so if it contains any errors or my concepts are wrong, indicate it and I will correct it, please.

    
answered by 23.06.2017 в 15:38
0

It could be something like

var cosnutla = from fac in db.Facturas
                join mov in db.MovimientosDineros on fac.IDFactura equals mov.IdFactura into g
                select new {
                    factura = fac,
                    Monto = g.Sum(x=>x.Valor)
                };

There are several ways to perform a left join, you could only group by the union

Perform grouped joins

or you may also apply conditions

Perform left outer joins

    
answered by 06.06.2017 в 08:36
0

You can try something like that with lambda expressions:

var q = Facturas.Select(x =>
            new {
                Id = x.IDFactura,
                Sum = MovimientosDineros.Where(y => y.IDFactura == x.IDFactura).Sum(z => z.Valor)
            }
        ).ToList();

We simply go through the table of Invoices and for each one of them we will generate a new object, which will have as id the table id invoices and a field called Sum, which will contain the sum of all the values of the table MovementsMoney match the Id.

    
answered by 07.06.2017 в 14:02
0

and if you create a class with a decorator, you fill the objects from sql in a list (with your inner join) and then the list works with linq ex:

 listado.sum(x => x.MovimientosDineros).Tolist();
    
answered by 23.06.2017 в 15:46