Multiple Left Join with Linq and Defaults Values

Question

I'm trying to write a query that contains multiple left joins in linq in a c# .netcore 2.2 application. I've tried writing the query in linq but it is not properly retrieving all the rows. Query I'm trying to convert is as follows.

  select   ISNULL(b.Id, '00000000-0000-0000-0000-000000000000') as 'Bat Id', p.Id as 'ProductId',  p.RP, u.UnitName as 'UnitName', ISNULL(b.QTY,0) as 'BusQty', p.[Name] as 'Product Name'
  from  Products p left join Bat b
  ON p.Id = b.ProductId
  left join Units u on p.UOId = u.Id;

linq I have so far

var allProducts = (from p in _db.Products
             join s in _db.Bat on p.Id equals s.ProductId into ps
             from s in ps.DefaultIfEmpty()
            join u in _db.Units on p.UOId equals u.Id
            select new
             {
           BatId = s.Id == null ? Guid.NewGuid() : s.Id,
          RP = p.RP,
          BusQty = s.QTY == null ? 0 : s.QTY,
          ProductName = p.Name,
          UnitName = u.UnitName,
          ProductId = p.Id,
         }).ToList();

And what LINQ code do you have so far? – Charlieface Jul 19 '21 at 23:32 — Charlieface, Jul 19 '21 at 23:32
@Charlieface added my linq to the question – nightwolf555 Jul 19 '21 at 23:50 — nightwolf555, Jul 19 '21 at 23:50

score 3 · Accepted Answer · answered Jul 19 '21 at 23:54

You are missing DefaultIfEmpty() on Units, thereby turning it into an inner join

var allProducts = (
         from p in _db.Products
         join s in _db.Bat on p.Id equals s.ProductId into ps
         from s in ps.DefaultIfEmpty()
         join u in _db.Units on p.UOId equals u.Id into us
         from u in us.DefaultIfEmpty()
         select new
         {
          BatId = s.Id ?? Guid.NewGuid(),
          RP = p.RP,
          BusQty = s.QTY ?? 0,
          ProductName = p.Name,
          UnitName = u.UnitName,
          ProductId = p.Id,
         }).ToList();

Multiple Left Join with Linq and Defaults Values

1 Answers1