Sum Group By 的SQL语句改写为linq的示例

主表Equip_MaintenanceOrders的总成本金额等于成本明细子表的成本金额的总和， SQL语句如下：

;with costAmtTemp as
(
    select MaintenanceOrderId,Sum(ISNULL(TotalAmt,0)) as Amt from Equip_MaintenanceCollectMaterials with(nolock) 
    where MaintenanceOrderId='bfda03fc-c6b2-ce5c-d64e-3a153bd3473e'
    group by MaintenanceOrderId
)
update a set a.CostAmt=b.Amt
from  Equip_MaintenanceOrders a
left join costAmtTemp b on(a.Id=b.MaintenanceOrderId)
where a.Id='bfda03fc-c6b2-ce5c-d64e-3a153bd3473e'

改写为Linq如下：

var db = await GetDbContextAsync();

// 计算Equip_MaintenanceCollectMaterials中对应MaintenanceOrderId的TotalAmt总和
var costAmt = db.MaintenanceCollectMaterials
    .Where(m => m.MaintenanceOrderId == maintenanceOrderId)
    .GroupBy(m => m.MaintenanceOrderId)
    .Select(g => new { MaintenanceOrderId = g.Key, Amt = g.Sum(m => m.TotalAmt ?? 0) })
    .FirstOrDefault();

// 获取Equip_MaintenanceOrders中对应的记录
var maintenanceOrder = db.MaintenanceOrders
    .SingleOrDefault(a => a.Id == maintenanceOrderId);

// 如果找到了对应的记录，则更新CostAmt
if (maintenanceOrder != null && costAmt != null)
{
   maintenanceOrder.CostAmt = costAmt.Amt;
   db.SaveChanges(); // 保存更改
}