join not returning all elements in left table C# lambda

Question

I have 2 tables, the left table has data like this:

I do a left join with another table with the following expression:

 var result = posicion.Join(fact,
                            p => p.Cod_articulo,
                            f => f.Cod_articulo,
                            (p, f) => new { p.Posicion, p.Cant_historico, 
                                            p.Cod_articulo, f.Cantidad_facturada });

The problem is that the results don't include some items from the left table as seen below:

As you can see in the result there is no data for position 3, 6 etc. what would be missing from my join?

Answer 1

You need to do group join (that is left join in Linq). It's better to do with query syntax:

from p in posicion
join f in fact
    on p.Cod_articulo equals f.Cod_articulo into g // GroupJoin
from pf in g.DefaultIfEmpty()
select new { 
   p.Posicion, 
   p.Cant_historico, 
   p.Cod_articulo, 
   Cantidad_facturada = (pf == null) ? null : pf.Cantidad_facturada 
}

This query selects all facts corresponding to posicion p into group g. Then from each group we select results, even if there is no corresponding facts for current posicion (that is DefaultIfEmpty case).

Lambda syntax will be much less readable:

posicion.GroupJoin(fact,
                   p => p.Cod_articulo,
                   f => f.Cod_articulo,
                  (p, g) => new { p, g })
        .SelectMany(x => x.g.DefaultIfEmpty(), (x, pf) => new {
                x.p.Posicion, 
                x.p.Cant_historico, 
                x.p.Cod_articulo, 
                Cantidad_facturada = (pf == null) ? null : pf.Cantidad_facturada 
        });

Consider also reading this MSDN article: How to: Perform Left Outer Joins