LINQ查询因ReturnQty返回Null：求SQL转LINQ或修改方案

问题背景 #

编写的LINQ查询意图获取PurchaseQty、ReturnQty、SaleQty，但整体返回Null。排查发现是ReturnQty字段导致的问题——注释掉该字段后，PurchaseQty和SaleQty可正常获取。原LINQ代码如下：

var result = await (from data in _dbContext.MaterialInwardBarcodes
                    where data.VendorId == vendorId && data.Date >= fromDate && data.Date <= toDate
                    join product in _dbContext.Products on data.ProductId equals product.Id
                    where product.CategoryId == categoryId
                    join material in _dbContext.MaterialInwardDetails on new { ProductId = data.ProductId, MaterialInwardHeaderId = data.RefId } equals new { material.ProductId, material.MaterialInwardHeaderId } into materialGroup
                    from material in materialGroup.DefaultIfEmpty()
                    join barcode in _dbContext.BarcodePost on data.Barcode equals barcode.BarcodeNumber into barcodeGroup
                    from barcode in barcodeGroup.DefaultIfEmpty()
                    join returnDetails in _dbContext.MaterialReturnNoteDetails on barcode.RefId equals returnDetails.MaterialReturnNoteHeaderId into rDetailsGroup
                    from returnDetails in rDetailsGroup.DefaultIfEmpty()
                    join invoice in _dbContext.InvoiceHeader on barcode.RefId equals invoice.BucketId into invoiceGroup
                    from invoice in invoiceGroup.DefaultIfEmpty()
                    join iDetails in _dbContext.InvoiceDetails on invoice.Id equals iDetails.InvoiceHeaderId into iDetailsGroup
                    from iDetails in iDetailsGroup.DefaultIfEmpty()
                    group new { data, material, iDetails, returnDetails } by new { data.Date, data.ProductId, data.Barcode } into grouped
                    select new VendorPaymentDetails()
                    {
                        Date = grouped.Key.Date,
                        ProductId = grouped.Key.ProductId,
                        PurchaseQty = grouped.Sum(x => (decimal?)x.material.Quantity ?? 0.0m),
                        ReturnQty = grouped.Sum(x => (decimal?)x.returnDetails.Quantity ?? 0.0m),
                        SaleQty = grouped.Sum(x => (decimal?)x.iDetails.Quantity ?? 0.0m)
                    }
                   ).ToListAsync();

已提供可正常运行的SQL查询：

SELECT
    date_trunc('day', m."Date", 'UTC') AS "Date",
    m."ProductId",
    COALESCE(SUM(CASE WHEN m0."Quantity" IS NOT NULL THEN m0."Quantity" ELSE 0 END), 0.0) AS "PurchaseQty",
    COALESCE(SUM(CASE WHEN r0."Quantity" IS NOT NULL THEN r0."Quantity" ELSE 0 END), 0.0) AS "ReturnQty",
    COALESCE(SUM(CASE WHEN i0."Quantity" IS NOT NULL THEN i0."Quantity" ELSE 0 END), 0.0) AS "SaleQty"
FROM "MaterialInwardBarcodes" AS m
LEFT JOIN "MaterialInwardDetails" AS m0 ON m."ProductId" = m0."ProductId" AND m."RefId" = m0."MaterialInwardHeaderId"
LEFT JOIN "BarcodePost" AS b ON m."Barcode" = b."BarcodeNumber"
LEFT JOIN "MaterialReturnNoteDetails" AS r0 ON b."RefId" = r0."MaterialReturnNoteHeaderId"
LEFT JOIN "InvoiceHeader" AS i ON b."RefId" = i."BucketId"
LEFT JOIN "InvoiceDetails" AS i0 ON i."Id" = i0."InvoiceHeaderId"
WHERE m."VendorId" = '52e4bd92-c9de-4d0a-87c7-80f45ce17117'
  AND date_trunc('day', m."Date", 'UTC') >= '2023-08-28T00:00:00Z'
  AND date_trunc('day', m."Date", 'UTC') <= '2023-08-31T00:00:00Z'
GROUP BY date_trunc('day', m."Date", 'UTC'), m."ProductId", m."Barcode" 

问题根源 #

原LINQ中，当barcode为Null（左连接BarcodePost无匹配结果时），直接访问barcode.RefId会触发空引用异常，导致整个查询返回Null。而SQL的左连接逻辑会自动处理Null值，不会中断查询。

修改方案（与SQL逻辑完全对齐） #

以下是严格按照提供的SQL转换而来的LINQ查询，同时修复了空引用问题：

var result = await (from m in _dbContext.MaterialInwardBarcodes
                    where m.VendorId == vendorId 
                          && EF.Functions.DateTrunc("day", m.Date, "UTC") >= fromDate 
                          && EF.Functions.DateTrunc("day", m.Date, "UTC") <= toDate
                    // 左连接MaterialInwardDetails
                    join m0 in _dbContext.MaterialInwardDetails 
                        on new { m.ProductId, m.RefId } equals new { m0.ProductId, m0.MaterialInwardHeaderId } into materialGroup
                    from m0 in materialGroup.DefaultIfEmpty()
                    // 左连接BarcodePost
                    join b in _dbContext.BarcodePost 
                        on m.Barcode equals b.BarcodeNumber into barcodeGroup
                    from b in barcodeGroup.DefaultIfEmpty()
                    // 左连接MaterialReturnNoteDetails，处理barcode为Null的情况
                    join r0 in _dbContext.MaterialReturnNoteDetails 
                        on b?.RefId equals r0.MaterialReturnNoteHeaderId into returnGroup
                    from r0 in returnGroup.DefaultIfEmpty()
                    // 左连接InvoiceHeader，处理barcode为Null的情况
                    join i in _dbContext.InvoiceHeader 
                        on b?.RefId equals i.BucketId into invoiceGroup
                    from i in invoiceGroup.DefaultIfEmpty()
                    // 左连接InvoiceDetails，处理invoice为Null的情况
                    join i0 in _dbContext.InvoiceDetails 
                        on i?.Id equals i0.InvoiceHeaderId into invoiceDetailsGroup
                    from i0 in invoiceDetailsGroup.DefaultIfEmpty()
                    // 按SQL指定字段分组
                    group new { m0, r0, i0 } by new 
                    { 
                        Date = EF.Functions.DateTrunc("day", m.Date, "UTC"), 
                        m.ProductId, 
                        m.Barcode 
                    } into grouped
                    select new VendorPaymentDetails()
                    {
                        Date = grouped.Key.Date,
                        ProductId = grouped.Key.ProductId,
                        PurchaseQty = grouped.Sum(x => (decimal?)x.m0.Quantity ?? 0.0m),
                        ReturnQty = grouped.Sum(x => (decimal?)x.r0.Quantity ?? 0.0m),
                        SaleQty = grouped.Sum(x => (decimal?)x.i0.Quantity ?? 0.0m)
                    }).ToListAsync();

关键修改点 #

1. 使用EF.Functions.DateTrunc实现SQL中的date_trunc函数，确保日期过滤和分组逻辑完全一致
2. 在关联MaterialReturnNoteDetails、InvoiceHeader、InvoiceDetails时，使用b?.RefId、i?.Id处理Null值，避免空引用异常
3. 移除原LINQ中多余的Products表关联（若业务需要按Category过滤，可重新添加join product in _dbContext.Products on m.ProductId equals product.Id及where product.CategoryId == categoryId）
4. 分组字段与SQL完全对齐，保证聚合结果的正确性

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