Subtract two tables with linq

Question

This is my first post and I hope you can help me. I didn't find an answer so here I'm:

I created this query in SQL and it works.

string consultaSQL =
                    @"SELECT a.GastosEstudio - ISNULL(SUM(b.GastosEstudioR),0) AS restagastos, a.Articulo - ISNULL(SUM(b.ArticuloR),0) AS restaarticulo, a.Honorarios - ISNULL(SUM(b.HonorariosR),0) AS restahonorarios, a.IVAHonorarios - ISNULL(SUM(b.IVAHonorariosR),0) AS restaivahonorarios FROM deudores a LEFT JOIN recibos b ON a.DNI=b.DNI WHERE a.DNI = @DNI GROUP BY a.GastosEstudio, a.Articulo, a.Honorarios, a.IVAHonorarios";

Now I need to do the same but in LINQ. Basically: I have two tables (deudores and recibos). In deudores I have the debt with the different concepts (columns):

gastos, articulo, honorarios, ivahonorarios

In the table recibos I insert the receipts with the same columns.

The SQL query sums the receipts and subtracts the debt. The closest I get in LINQ was this:

var query = (from d in bd.deudores                         
             join r in bd.recibos on d.DNI equals r.DNI
             where d.DNI == DNI
             group d by d.DNI into g
             select new
                     {
                         DNI = g.Key,
                         articulo = g.Max(x => x.Articulo) - g.Sum(x => x.ArticuloR),
                         gastos = g.Max(x => x.GastosEstudio) - g.Sum(x => x.GastosEstudioR),
                         honorarios = g.Max(x => x.Honorarios) - g.Sum(x => x.HonorariosR),
                         ivah = g.Max(x => x.IVAHonorarios) - g.Sum(x => x.IVAHonorariosR),
                     });

The problem with this query is that if there is no receipt does not show any information (should show the initial debt)

I try with DefaultIfEmpty but didn't work:

var query = (from d in bd.deudores
             join r in bd.recibos on d.DNI equals r.DNI into Pagos
             from p in Pagos.DefaultIfEmpty()
             where d.DNI == DNI
             group d by d.DNI into g
             select new
 {
     DNI = g.Key,
     articulo = g.Max(x => x.Articulo) - g.SelectMany(x => x.recibos).Count() >= 1
        ? g.SelectMany(x => x.recibos).Sum(y => y.ArticuloR)
        : 0,
     gastos = g.Max(x => x.GastosEstudio) - g.SelectMany(x => x.recibos).Count() >= 1
            ? g.SelectMany(x => x.recibos).Sum(y => y.GastosEstudioR)
        : 0,
     honorarios = g.Max(x => x.Honorarios) - g.SelectMany(x => x.recibos).Count() >= 1
        ? g.SelectMany(x => x.recibos).Sum(y => y.HonorariosR)
        : 0,
     ivah = g.Max(x => x.IVAHonorarios) - g.SelectMany(x => x.recibos).Count() >= 1
        ? g.SelectMany(x => x.recibos).Sum(y => y.IVAHonorariosR)
        : 0
 });

The problem with this query is that it does not subtract it.

Any suggestion?

Thank you!

Try changing `from p in Pagos.DefaultIfEmpty()` to `from r in Pagos.DefaultIfEmpty()` — Martin, Apr 11 '17 at 20:36

score 1 · Answer 1 · edited May 23 '17 at 11:54

You want the equivalent of an outer join, so you correctly turn to a GroupJoin, or join ... into. But the query part ...

 from d in bd.deudores
 join r in bd.recibos on d.DNI equals r.DNI into Pagos
 from p in Pagos.DefaultIfEmpty()
 where d.DNI == DNI
 group d by d.DNI into g

... does more than you want. In fluent LINQ syntax its structure is equivalent to

bd.deudores.GroupJoin(bd.recibos, ...)
           .SelectMany(...)
           .GroupBy(...)

The point is that the first GroupJoin creates a collection of deudores, each having a group of their recibos, that may be empty. Then the SelectMany flattens it into pairs of one deudores and one recibos or null. Subsequently, the GroupBy creates groups with null elements.

The first GroupJoin is all you need:

from d in bd.deudores
join r in bd.recibos on d.DNI equals r.DNI into g
select new
{
    DNI = d.DNI,
    articulo = d.Articulo - g.Select(x => x.ArticuloR).DefaultIfEmpty().Sum(),
    gastos = d.GastosEstudio - g.Select(x => x.GastosEstudioR).DefaultIfEmpty().Sum(),
    honorarios = d.Honorarios - g.Select(x => x.HonorariosR).DefaultIfEmpty().Sum(),
    ivah = d.IVAHonorarios - g.Select(x => x.IVAHonorariosR).DefaultIfEmpty().Sum()
});

By adding DefaultIfEmpty() it is ensured that Sum will return 0 when there are no elements.

I tried but it does not work. The first part of `select new` in the `articulo = g.Max(x => x.Articulo)` the part of `Articulo` is underlaing in a red line (i don't know how to post an image with the error) — German W, Apr 12 '17 at 01:58
The message is something like this (i use google translantor: `recibos` does not contain a definition for `Articulo` nor is there any extension method `Articulo` that accepts a first `recibos` type argument (is there no using directive or an assembly reference?) — German W, Apr 12 '17 at 02:05
What is the relationship between `deudores` and `recibos`? Maybe the first thing you should do is create navigation properties between them so it's possible to do the query without the (LINQ) join statement. — Gert Arnold, Apr 12 '17 at 09:16

score 0 · Answer 2 · answered Apr 12 '17 at 13:04

@Gert Arnold: The relationship between the two tables is a column name DNI. In the table deudores is PK and in the table recibos is FK. Last night i tried this code and it works:

var query = (from d in bd.deudores 
         join r in bd.recibos 
         on d.DNI equals r.DNI into g
         where d.DNI == DNI 
         select new 
         {
          articulo = d.Articulo - g.Sum(x => x.ArticuloR) ?? d.Articulo,
          gastos = d.GastosEstudio - g.Sum(x => x.GastosEstudioR) ?? d.GastosEstudio,
          honorarios = d.Honorarios - g.Sum(x => x.HonorariosR) ?? d.Honorarios,
          ivah = d.IVAHonorarios - g.Sum(x => x.IVAHonorariosR) ?? d.IVAHonorarios
          });

Is it the best way to do it ?. If you want to give me your opinion will be welcome.

Regards!

Subtract two tables with linq

2 Answers2