ASP.NET MVC中用LINQ自连接优化同表发票totalAmount关联查询

问题背景 #

• ASP.NET MVC场景下，发票表中每条业务发票对应两条记录：共享相同GatePassNo，但InvoiceType不同
• InvoiceType = Sales的记录包含有效TotalAmount值，InvoiceType = DeliveryChallan的记录TotalAmount为null
• 需求：查询DeliveryChallan类型发票时，关联获取对应Sales类型发票的TotalAmount，且必须保持查询为IQueryable状态以支持后续排序，禁止提前转为List
• 当前实现方案效率低下，需寻求最优LINQ自连接方案

现有实现代码 #

var query = _invoiceService.Table.Include("Items").Include("Customer").Include("Suppliers").Include("CreatedBy")
                    .Where(i => i.CompanyId == companyId);

switch (invoiceType)
{
    case InvoiceType.Sales:
        if (customerId != null)
            query = query.Where(p => p.CustomerId == customerId);
        if (deliveryOrderNo != null && deliveryOrderNo > 0)
        {
            var deliveryChallan = query.FirstOrDefault(p => p.InvoiceType == InvoiceType.DeliveryChallan && p.GatePassNo.ToString().EndsWith(deliveryOrderNo.ToString()));
            query = deliveryChallan != null ? query.Where(p => p.InvoiceType == InvoiceType.Sales && p.GatePassNo == deliveryChallan.GatePassNo) : query.Where(p => p.InvoiceType == InvoiceType.Sales);
        }
        else
            query = query.Where(p => p.InvoiceType == InvoiceType.Sales);
        break;
    
    case InvoiceType.DeliveryChallan:
        query = query.Where(p => p.InvoiceStatus != InvoiceStatus.Void);
        if (invoiceNo != null && invoiceNo > 0)
        {
            var invoice = query.FirstOrDefault(p => p.InvoiceType == InvoiceType.Sales && p.InvoiceNo.ToString().EndsWith(invoiceNo.ToString()));

            query = invoice != null ? query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan && p.GatePassNo == invoice.GatePassNo) : query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan);
        }
        else
        {
            query = query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan);

            // 此处需关联获取对应Sales发票的TotalAmount并映射到当前查询的发票中
        }

        if (customerId != null)
            query = query.Where(p => p.CustomerId == customerId);
        if (_workContextService.IsMaster() && customerId.HasValue)
            showAll = true;
        break;
}

此前的低效方案 #

该方案通过循环查询导致N+1数据库请求，性能低下且无法保持IQueryable状态：

public void RetrieveTotalInvoiceAmount(List<Invoice> invoices)
{
    foreach (var item in invoices)
    {
        var result = _invoiceService.Table.Where(i => i.GatePassNo == item.GatePassNo && i.InvoiceType == InvoiceType.Sales).FirstOrDefault();
        if (result != null)
        {
            item.TotalAmount = result.TotalAmount;
        }
    }
}

优化后的LINQ自连接方案 #

通过左自连接实现一次性关联查询，全程保持IQueryable状态，避免N+1问题，同时支持后续排序、分页等操作。

修改InvoiceType.DeliveryChallan分支中的else代码块：

else
{
    // 使用左连接关联Sales类型发票，映射TotalAmount
    query = from dc in query.Where(p => p.InvoiceType == InvoiceType.DeliveryChallan)
            join sale in _invoiceService.Table.Where(s => s.InvoiceType == InvoiceType.Sales)
            on dc.GatePassNo equals sale.GatePassNo into salesGroup
            from matchedSale in salesGroup.DefaultIfEmpty()
            select new Invoice
            {
                // 复制DeliveryChallan发票的所有属性
                Id = dc.Id,
                GatePassNo = dc.GatePassNo,
                InvoiceType = dc.InvoiceType,
                InvoiceStatus = dc.InvoiceStatus,
                CustomerId = dc.CustomerId,
                CompanyId = dc.CompanyId,
                CreatedDate = dc.CreatedDate,
                CreatedBy = dc.CreatedBy,
                // 保留已Include的导航属性
                Customer = dc.Customer,
                Suppliers = dc.Suppliers,
                Items = dc.Items,
                // 映射对应Sales发票的TotalAmount
                TotalAmount = matchedSale?.TotalAmount
            };
}

方案说明 #

1. 左连接逻辑：通过join ... into ... DefaultIfEmpty()实现左连接，确保即使没有对应Sales发票的DeliveryChallan也能被保留
2. IQueryable保持：整个查询始终为IQueryable类型，后续可直接添加OrderBy、Skip/Take等排序分页操作
3. 性能优化：一次性生成关联SQL语句，仅执行一次数据库查询，彻底解决循环查询的N+1性能问题
4. 投影映射：通过实体投影直接将Sales发票的TotalAmount赋值给DeliveryChallan记录，无需后续内存中修改

内容的提问来源于stack exchange，提问作者Shahreyar Butt