c# - 如何选择 'product' 行而不是 'sale' 行

Question

在我下面的 LINQ 查询中，我想选择“产品”行并将“销售”行数据添加到其中，但发生了相反的情况，它选择了“销售”行并添加了“产品”行

var query = (from product in SampleProductTable
from sale in SampleSalesTable
where (sale.ProductId == product.Id)
select new
{
    Id = product.Id,
    TotalSales = product.TotalSales + ((product.Id == sale.ProductId) ? sale.Amount : 0)
})

示例产品表

+-------+------------+---------+-----------------+-------+------------+
|  Id   | CategoryId | BrandId |      Name       | Price | TotalSales |
+-------+------------+---------+-----------------+-------+------------+
| mlk3  | MLK        | BRND1   | Creamy Milk     |     5 |         10 |
| snck2 | SNCK       | BRND2   | Chocolate Snack |     2 |         24 |
+-------+------------+---------+-----------------+-------+------------+

示例销售表

+-----+-----------+--------+
| Id  | ProductId | Amount |
+-----+-----------+--------+
| 120 | mlk3      |     55 |
| 121 | mlk3      |     15 |
| 122 | snck2     |     12 |
| 123 | mlk3      |      5 |
| 124 | mlk3      |     10 |
| 125 | snck2     |      2 |
| 126 | mlk3      |    115 |
| 127 | snck2     |      6 |
| 128 | snck2     |     34 |
+-----+-----------+--------+

期望的输出

+-------+------------+
|  Id   | TotalSales |
+-------+------------+
| mlk3  |        210 |
| snck2 |         78 |
+-------+------------+

Answer 1

var answer = (from product in SampleProductTable
              join sale in SampleSalesTable on product.Id == sale.ProductId into subSales
              from subSale in subSales.DefaultIfEmpty()
              group subSale by new { product.Id, product.TotalSales } into gr
              select new 
              {
                  gr.Key.Id,
                  TotalSales = gr.Sum(x => x == null ? 0 : x.Amount) + gr.Key.TotalSales
              }).ToList();

近似的 T-SQL:

select p.Id, p.TotalSales + sum(coalesce(s.Amount, 0)) TotalSales 
from SampleProductTable p
left outer join SampleSalesTable s on p.Id = s.ProductId 
group by p.Id, p.TotalSales