C# Linq Group, Count & Sum

Question

I am looking for help with a Linq query I am struggling with. I am trying to build a page that shows the current stock level of all products based on the stock that has been added and the items that have been sold. These are my tables:

Product
Id
Code
Name

Stock
Id
ProductId
Quantity

Sold
Id
ProductCode

I would like the page to show the following columns:

Stock added - sum of the quantity field of all related rows from the stock table.

Items sold - count of all related rows from the Sold table

Stock remaining = stock added - items sold.

This is my attempt at the Linq query so far:

return await (from product in _context.Products
                      join stock in _context.Stocks on product.Id equals stock.ProductId
                      join so in _context.SaleProducts on product.Code equals so.Code into sold
                      from subSold in sold.DefaultIfEmpty()
                      group new { product, stock, sold } by product into g
                      select new StockViewModel
                      {
                          ProductId = g.Key.Id,
                          ProductCode = g.Key.Code,
                          ProductName = g.Key.Name,
                          StockAdded = g.Sum(x => x.stock.Quantity),
                          ItemsSold = g.Select(x => x.sold).Count()
                      })
                      .ToArrayAsync();

The items sold is showing the same value for each row and it is only showing products that have had stock added.

I would like to show all products whether stock has been added or not. I would also like to show all products whether any have been sold or not

Answer 1

Thanks to @T N for the heads up regarding Linq subqueries, this is the query I was looking for:

var stockRecords = await (from p in _context.Products
                                  select new StockViewModel
                                  {
                                      ProductId = p.Id,
                                      ProductName = p.Name,
                                      StockAdded = (
                                        (from s in _context.Stocks
                                         where s.ProductId == p.Id
                                         select s.Quantity).Sum()
                                      ),
                                      ItemsSold = (
                                        (from s in _context.SaleProducts
                                         where s.Code == p.Code
                                         select s).Count()
                                      )
                                  })
                          .ToArrayAsync();