如何将sql转换为LINQ组合左JOIN, WHERE, GROUP BY和SUM在LINQ?

Question

我的sql查询:

SELECT 
a.`ItemCode`, a.`UnitCost`, a.`UOM`, a.`Description`,
SUM(b.QtyIN) AS 'Total', a.`UnitCost` AS 'Adjusted cost' 
FROM 
stk_master AS a 
LEFT JOIN 
(SELECT * 
FROM stk_detail_trn_in 
WHERE `LocationCode` = 'PGV001' 
AND `DocType3` = 'OS-NSI') AS b ON a.`ItemCode` = b.`ItemCode` 
GROUP BY 
a.`ItemCode`;

这是LINQ查询:

var data = (from e in master
join d in stockIn on e.ItemCode equals d.ItemCode into table1
from d in table1.DefaultIfEmpty()
where d.LocationCode == id && d.DocType3 == "OS-NSI"
select new ViewModelListInventory
{
stk_master = e,
stk_detail_trn_in = d,
})
.GroupBy(x => x.stk_detail_trn_in.ItemCode) 
.Select(d => new stk_detail_trn_in { QtyIN = d.Sum(x => x.stk_detail_trn_in.QtyIN)})
.ToList();

Answer 1

像这样:

var query = 
from e in master
join d in stockIn.Where(d => d.LocationCode == id && d.DocType3 == "OS-NSI") on e.ItemCode equals d.ItemCode into table1
from d in table1.DefaultIfEmpty()
group d by new { e.ItemCode } into g
select new 
{
g.Key.ItemCode,
Total = g.Sum(x => x.QtyIN)
};