LINQ查询优化疑难：关联ChargeableItem后结果行数减少且查询缓慢

一、结果行数减少的原因与修复 #

原查询将OwnerId、ChargeTarget、IsDeleted放在join的on子句中，这是左外连接（DefaultIfEmpty()）的正确逻辑：只有匹配这些条件的ChargeableItemEntities记录才会关联，没有匹配项的BookingItem依然会被保留（此时ci为null）。

而修改后的代码把这些条件移到了where子句：

where
ci.OwnerId == ServerUser.PartyId.Value
&& ci.ChargeTarget == criteria.Target.Value
&& !ci.IsDeleted

当左外连接无匹配项时ci为null，这几个null比较都会返回false，直接过滤掉原本该保留的BookingItem，导致结果行数减少。

修复方案 #

方案1（推荐，与原逻辑完全一致）

将条件放回join的on子句：

from b in sourceBookingItemsQuery
join ci in Context.ChargeableItemEntities 
on new { b.Id, OwnerId = ServerUser.PartyId.Value, ChargeTarget = criteria.Target.Value, IsDeleted = false }
equals new { Id = ci.BookingItemId.Value, ci.OwnerId, ci.ChargeTarget, ci.IsDeleted } into chargeable
from ci in chargeable.DefaultIfEmpty()
// 后续保留原where条件，无需添加ci的非空判断

方案2（若需保留where子句写法）

增加ci为null的判断逻辑：

from b in sourceBookingItemsQuery
join ci in Context.ChargeableItemEntities on b.Id equals ci.BookingItemId.Value into chargeable
from ci in chargeable.DefaultIfEmpty()
where
(ci == null || (ci.OwnerId == ServerUser.PartyId.Value && ci.ChargeTarget == criteria.Target.Value && !ci.IsDeleted))
// 追加原查询的其他where条件

二、查询性能优化方案 #

原查询耗时超20000ms，核心问题是重复子查询、导航属性懒加载、低效关联逻辑，以下是针对性优化：

1. 消除重复的ChargeEntities子查询 #

原查询两次查询ChargeEntities（一次用于过滤已开票记录，一次用于聚合未开票费用），可预先聚合数据避免重复查询：

// 预查询符合条件的Charge记录并分组
var chargeGroups = Context.ChargeEntities
    .Where(y => y.OwnerId == this.CurrentPartyId
             && y.Type.Target == criteria.Target
             && y.CounterpartRole == ChargeCounterpartRole.Client
             && (y.ItemType == InvoiceItemType.BookingService || y.ItemType == InvoiceItemType.ShippingUnit)
             && !y.IsDeleted)
    .GroupBy(y => new { y.ParcelId, y.InvoiceId, y.Currency.Id })
    .Select(g => new 
    {
        g.Key.ParcelId,
        g.Key.InvoiceId,
        CurrencyId = g.Key.Id,
        TotalAmount = g.Sum(c => c.Amount),
        CurrencySymbol = g.First().Currency.Symbol,
        Target = g.First().Type.Target,
        ItemId = g.First().ItemId
    })
    .ToList();

// 拆分已开票包裹ID集合、未开票费用分组
var invoicedParcels = chargeGroups.Where(c => c.InvoiceId != null).Select(c => c.ParcelId).ToHashSet();
var unInvoicedCharges = chargeGroups.Where(c => c.InvoiceId == null)
    .GroupBy(c => new { c.ParcelId, c.CurrencyId })
    .Select(g => new BillableItemCharge
    {
        Amount = g.Sum(c => c.TotalAmount),
        Currency = new CurrencyEntry { Id = g.Key.CurrencyId, Symbol = g.First().CurrencySymbol },
        Target = g.First().Target,
        InvoiceId = g.First().InvoiceId,
        ItemId = g.First().ItemId
    })
    .ToList();

主查询中直接使用预聚合数据：

var items = (
    from b in sourceBookingItemsQuery
    // 保留原join逻辑
    join ci in Context.ChargeableItemEntities 
    on new { b.Id, OwnerId = ServerUser.PartyId.Value, ChargeTarget = criteria.Target.Value, IsDeleted = false }
    equals new { Id = ci.BookingItemId.Value, ci.OwnerId, ci.ChargeTarget, ci.IsDeleted } into chargeable
    from ci in chargeable.DefaultIfEmpty()
    where
        (criteria.Client == null || ci.ClientId == criteria.Client)
        && (criteria.IsLoaded == null || (criteria.IsLoaded.Value == (b.LoadingCode == "Y" || b.LoadingCode == "B")))
        && (criteria.LoadedFrom == null || b.ShippedOn >= criteria.LoadedFrom)
        && (criteria.LoadedTo == null || b.ShippedOn <= criteria.LoadedTo)
        && (criteria.BookedFrom == null || b.Booking.CreatedOn >= criteria.BookedFrom)
        && (criteria.BookedTo == null || b.Booking.CreatedOn <= criteria.BookedTo)
        && (criteria.SerialNumber == null || b.Parcel.SerialNumber == criteria.SerialNumber)
        && !b.IsCanceled
        && !invoicedParcels.Contains(b.ParcelId) // 替换原Any()子查询
    orderby b.Id descending
    select new 
    {
        b.Id,
        b.OwnerId,
        Parcel = b.Parcel,
        Pod = b.Pod,
        Pol = b.Booking.Pol,
        ci,
        b.Voyage,
        b.ShippedOn,
        b.ParcelId
    })
    .AsEnumerable()
    .Select(x => new BillableItemDetail()
    {
        Id = x.Id,
        OwnerId = x.OwnerId,
        Parcel = new ParcelReference() { Id = x.Parcel.Id, Description = x.Parcel.Description, SerialNumber = x.Parcel.SerialNumber },
        Pod = new PortReference() { Id = x.Pod.Id, Code = x.Pod.Code, Name = x.Pod.Description },
        Pol = new PortReference() { Id = x.Pol.Id, Code = x.Pol.Code, Name = x.Pol.Description },
        Client = x.ci != null ? new PartyReference() { Id = x.ci.ClientId.Value, Name = x.ci.Client.Name } : null,
        VoyageCode = x.Voyage != null ? x.Voyage.Code : null,
        ShippedOn = x.ShippedOn,
        Charges = unInvoicedCharges.Where(c => c.ItemId == x.Id).ToArray()
    })
    .ToList();

2. 优化导航属性加载，避免N+1查询 #

原查询直接访问b.Parcel、b.Pod等导航属性会触发懒加载，需在sourceBookingItemsQuery中提前Include关联：

var sourceBookingItemsQuery = Context.BookingItemEntities
    .Include(b => b.Parcel)
    .Include(b => b.Pod)
    .Include(b => b.Booking)
        .ThenInclude(booking => booking.Pol)
    .Include(b => b.Voyage)
    // 追加原sourceBookingItemsQuery的过滤条件

3. 添加数据库索引 #

针对查询中频繁过滤、关联的字段创建组合索引：

• ChargeableItemEntities：BookingItemId、OwnerId、ChargeTarget、IsDeleted
• ChargeEntities：OwnerId、Type.Target、CounterpartRole、ItemType、IsDeleted、ParcelId、InvoiceId
• BookingItemEntities：ShippedOn、IsCanceled、ParcelId、LoadingCode
• BookingEntities：CreatedOn、PolId

4. 简化左外连接逻辑 #

若ChargeableItemEntities中一个BookingItemId对应多条记录，可提前去重减少关联数据量：

var filteredChargeableItems = Context.ChargeableItemEntities
    .Where(ci => ci.OwnerId == ServerUser.PartyId.Value
              && ci.ChargeTarget == criteria.Target.Value
              && !ci.IsDeleted)
    .GroupBy(ci => ci.BookingItemId.Value)
    .Select(g => g.First()) // 按业务需求取对应记录，如最新/第一条
    .ToList();

// 主查询join预过滤集合
from b in sourceBookingItemsQuery
join ci in filteredChargeableItems on b.Id equals ci.BookingItemId.Value into chargeable
from ci in chargeable.DefaultIfEmpty()

5. 确保排序在数据库端执行 #

若数据量较大，orderby b.Id descending需在数据库端执行，避免在AsEnumerable()之后排序（客户端排序效率极低）。

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