LEFT JOIN three tables and SUM in LINQ

Question

I have three tables:

products purchased (RecordEntered as A)
products sold in the country (SoldInCountry as B)
products sold outside the country (SoldOutCountry as C)

Each record in A could be:

entered and not yet sold
entered and sold only in the country
entered and sold only out of the country
entered and sold in the country and also outside the country

I started grouping the pieces in table B like so:

SELECT        
A.IdRecord, A.Qty, sum(isnull(B.Qty,0)) AS Expr1
FROM            
RecordEntered AS A 
LEFT OUTER JOIN
SoldInCountry AS B ON A.IdRecord = B.IdRecord
group by A.IdRecord, A.Qty

But I do not know how to go on.

I would like a query to show me how many pieces I still have in stock. Like this:

A.Qty - (SUM(ISNULL(B.Qty, 0)) + SUM(ISNULL(C.Qty, 0)))

I wrote an example in SQL, but the goal is LINQ:

from a in _ctx.....
  where .....
  select...

thanks

score 1 · Accepted Answer · answered Jul 03 '17 at 20:12

It isn't easy to do a full outer join in LINQ (see my answer here: https://stackoverflow.com/a/43669055/2557128) but you don't need that to solve this:

var numInStock = from item in RecordEntered
             select new {
                 item.Code,
                 Qty = item.Qty - (from sic in SoldInCountry where sic.IdRecord == item.IdRecord select sic.Qty).SingleOrDefault() -
             (from soc in SoldOutCountry where soc.IdRecord == item.IdRecord select soc.Qty).SingleOrDefault()
             };

I assumed there would only be one sold record of each type for an item, if there could be more than one, you would need to Sum the matching records:

var numInStock = from item in RecordEntered
             select new {
                 item.Code,
                 Qty = item.Qty - (from sic in SoldInCountry where sic.IdRecord == item.IdRecord select sic.Qty).DefaultIfEmpty().Sum() -
             (from soc in SoldOutCountry where soc.IdRecord == item.IdRecord select soc.Qty).DefaultIfEmpty().Sum()
             };

LEFT JOIN three tables and SUM in LINQ

1 Answers1