如何在LINQ查询中从PurchaseOrder表正确获取Cost数据？

需要在以下C#异步方法的LINQ查询中为Cost字段赋值，计算满足purchaseOrder.Id == MaterialInward.PurchaseOrderId且MaterialInwardID == sList.RefId条件的PurchaseOrder表中Amount的总和。此前尝试关联poDetails并设置Cost = g.Sum(s => s.poDetails.Rate)但未得到任何结果。

代码如下：

public async Task<ResultModel<IEnumerable<GetStockReportDto>>> Handle(GetStockReportListQuery request, CancellationToken cancellationToken)
{
    var spec = new StockDateSpec(request._fromDate, request._toDate);
    var data = await _stockDetailsRepository.FindAsync(spec);
    var productList = await _productRepository.GetAllAsync();
    var materialDetails = await _materialInwardRepository.GetAllAsync();
    var materialHeader = await _materialInwardHeaderRepository.GetAllAsync();
    var invoiceDetails = await _invoiceRepository.GetAllAsync();
    var purchaseOrder = await _purchaseOrderRepository.GetAllAsync();

    var result = (
                    from sList in data
                    join pList in productList on sList.ProductId equals pList.Id
                    join qList in invoiceDetails on sList.RefId equals qList.InvoiceHeaderId
                    join mList in materialHeader on sList.RefId equals mList.Id
                    join poDetails in purchaseOrder on mList.PurchaseOrderId equals poDetails.Id
                    group new { sList, pList, qList, poDetails } by new { pList.Id, sList.Date.Date, qList.Rate } into g
                    select new GetStockReportDto()
                    {
                        ProductName = g.First().pList.Name,
                        OpeningQty = 0,
                        PurchaseQty = g.Where(s => s.sList.Type == ETypeEnum.Purchase).Sum(s => s.sList.Quantity),
                        Return = g.Where(s => s.sList.Type == ETypeEnum.Return).Sum(s => s.sList.Quantity),
                        SaleQty = -g.Where(s => s.sList.Type == ETypeEnum.Sale).Sum(s => s.sList.Quantity),
                        RemainingQty = g.Where(s => s.sList.Type == ETypeEnum.Purchase).Sum(s => s.sList.Quantity) - (-g.Where(s => s.sList.Type == ETypeEnum.Sale).Sum(s => s.sList.Quantity)),
                        Cost = ,
                        SaleAmt = g.Sum(s => s.qList?.Rate ?? 0),
                        PaymentAmt = 0,
                        Date = g.Key.Date
                    }
                    );
}

关键调整点 #

1. 补充分组关联对象：将mList加入分组对象中，确保后续能正确匹配MaterialInward与PurchaseOrder的关联条件
2. 修正Cost计算逻辑：针对分组内的采购类型记录，计算对应PurchaseOrder的Amount总和，同时匹配需求中的关联条件，并处理空值避免异常

修改后的完整代码 #

public async Task<ResultModel<IEnumerable<GetStockReportDto>>> Handle(GetStockReportListQuery request, CancellationToken cancellationToken)
{
    var spec = new StockDateSpec(request._fromDate, request._toDate);
    var data = await _stockDetailsRepository.FindAsync(spec);
    var productList = await _productRepository.GetAllAsync();
    var materialDetails = await _materialInwardRepository.GetAllAsync();
    var materialHeader = await _materialInwardHeaderRepository.GetAllAsync();
    var invoiceDetails = await _invoiceRepository.GetAllAsync();
    var purchaseOrder = await _purchaseOrderRepository.GetAllAsync();

    var result = (
                    from sList in data
                    join pList in productList on sList.ProductId equals pList.Id
                    join qList in invoiceDetails on sList.RefId equals qList.InvoiceHeaderId
                    join mList in materialHeader on sList.RefId equals mList.Id
                    join poDetails in purchaseOrder on mList.PurchaseOrderId equals poDetails.Id
                    // 将mList加入分组对象，保留关联关系
                    group new { sList, pList, qList, poDetails, mList } by new { pList.Id, sList.Date.Date, qList.Rate } into g
                    select new GetStockReportDto()
                    {
                        ProductName = g.First().pList.Name,
                        OpeningQty = 0,
                        PurchaseQty = g.Where(s => s.sList.Type == ETypeEnum.Purchase).Sum(s => s.sList.Quantity),
                        Return = g.Where(s => s.sList.Type == ETypeEnum.Return).Sum(s => s.sList.Quantity),
                        SaleQty = -g.Where(s => s.sList.Type == ETypeEnum.Sale).Sum(s => s.sList.Quantity),
                        RemainingQty = g.Where(s => s.sList.Type == ETypeEnum.Purchase).Sum(s => s.sList.Quantity) - (-g.Where(s => s.sList.Type == ETypeEnum.Sale).Sum(s => s.sList.Quantity)),
                        // 计算符合条件的PurchaseOrder Amount总和，过滤采购类型记录并处理空值
                        Cost = g.Where(s => s.sList.Type == ETypeEnum.Purchase 
                                        && s.sList.RefId == s.mList.Id 
                                        && s.mList.PurchaseOrderId == s.poDetails.Id)
                                .Sum(s => s.poDetails.Amount ?? 0),
                        SaleAmt = g.Sum(s => s.qList?.Rate ?? 0),
                        PaymentAmt = 0,
                        Date = g.Key.Date
                    }
                    );
}

问题说明 #

之前无结果大概率是两个原因：一是误用了Rate字段而非需求中的Amount字段；二是分组时未保留mList，导致无法匹配MaterialInwardID == sList.RefId的条件。

内容的提问来源于stack exchange，提问作者Farhan Patel