保留IQueryable特性关联ProductIsNotifyMe表的实现方案咨询

我负责电商系统的「到货通知」功能，现有一张ProductIsNotifyMe表，字段包括StoreID、MemberID、Sku（产品编码）、LocationId、Unit。同时系统中存在Product、ProductPricing等产品主表，其中Product表的AvailableIn字段以JSON格式存储产品的可用单位数据。

目前我有一个仅构建查询语句、不触发数据库调用的IQueryable LINQ查询（保留延迟执行特性），需要在此基础上关联ProductIsNotifyMe表，关联规则如下：

• 优先按StoreID、MemberID、Sku、LocationId、Unit五个字段精准匹配关联
• 若Unit字段无法匹配，则降级按前四个字段（StoreID、MemberID、Sku、LocationId）关联
• 查询结果中需要返回Sku及其对应的单位列表

现有原始LINQ查询代码如下：

return from prod in products

    join price in db.ProductPrices
        on new { prod.StoreId, prod.Sku, PriceSetCode = ctx.PriceSetCode.Value }
        equals new { price.StoreId, price.Sku, price.PriceSetCode } into prices
    from price in prices.DefaultIfEmpty()

    join custPrice in db.ProductCustomerPrices
        on new
        {
            prod.StoreId,
            prod.Sku,
            PriceSetCode = ctx.PriceSetCode.Value,
            AccountId = ctx.AccountId.Value.Value
        }
        equals new
        {
            custPrice.StoreId,
            custPrice.Sku,
            custPrice.PriceSetCode,
            custPrice.AccountId
        } into custPrices
    from custPrice in custPrices.DefaultIfEmpty()

    join custGroupPrice in db.ProductCustGroupPrices
        on new
        {
            prod.StoreId,
            prod.Sku,
            DiscountGroupCode = ctx.DiscountGroupCode,
            PriceSetCode = ctx.PriceSetCode.Value
        }
        equals new
        {
            custGroupPrice.StoreId,
            custGroupPrice.Sku,
            custGroupPrice.DiscountGroupCode,
            custGroupPrice.PriceSetCode
        } into custGroupPrices
    from custGroupPrice in custGroupPrices.DefaultIfEmpty()

    join locationStock in db.ProductLocations
        on new { prod.StoreId, prod.Sku, Id = ctx.LocationId }
        equals new { locationStock.StoreId, locationStock.Sku, locationStock.Id } into locationStocks
    from locationStock in locationStocks.DefaultIfEmpty()

    join lastOrdQty in db.LastOrderQty
        on new { prod.Sku, AccountId = ctx.AccountId.Value.Value }
        equals new { lastOrdQty.Sku, lastOrdQty.AccountId } into lastOrdQtys
    from lastOrdQty in lastOrdQtys.DefaultIfEmpty()

    join memberInfo in db.ProductMemberInfo
        on new { prod.StoreId, prod.Sku, MemberId = memId }
        equals new { memberInfo.StoreId, memberInfo.Sku, memberInfo.MemberId } into memberInfos
    from memberInfo in memberInfos.DefaultIfEmpty()

    join category in db.Categories
        on new { p = prod.PrimaryCategoryId ?? Guid.Empty }
        equals new { p = category.Id } into categoryInfos
    from categoryInfo in categoryInfos.DefaultIfEmpty()

    join substitute in db.ProductSubstitutes
        on new
        {
            prod.Sku
        }
        equals new
        {
            substitute.Sku
        } into prodSubstitute
    from substitute in prodSubstitute.DefaultIfEmpty()
    
    orderby prod.Sku

    select new ProductQuery
    {
        product = prod,
        price = price,
        custPrice = custPrice,
        categoryInfo = categoryInfo,
        custGroupPrice = custGroupPrice,
        onHand = locationStock.OnHand,
        onHandInf = locationStock.OnHand == null && locationStock.Id != null,
        limit = locationStock.SaleLimit,
        limitInf = locationStock.SaleLimit == null && locationStock.Id != null,
        locenabled = locationStock.Allowed && locationStock.Id != null,
        locsku = locationStock.AlternateSku,
        memberInfo = memberInfo,
        lastOrdQty = lastOrdQty.LastOrderQty, // Change For Last Order Quantity
        lastOrdDate = lastOrdQty.LastOrderTimestamp == null
            ? DateTimeOffset.Now
            : lastOrdQty.LastOrderTimestamp,
        substituteAvail = (substitute != null && !string.IsNullOrEmpty(substitute.ItemSub1)),
        substitutes = substitute != null ? new string[]
            {
                (string.IsNullOrEmpty(substitute.ItemSub1) ? string.Empty : substitute.ItemSub1),
                (string.IsNullOrEmpty(substitute.ItemSub2) ? string.Empty : substitute.ItemSub2),
                (string.IsNullOrEmpty(substitute.ItemSub3) ? string.Empty : substitute.ItemSub3)
            } : new string[0],
    };

改造方案及代码实现 #

要实现需求中的关联逻辑，可通过两次左连接+优先级判断的方式处理，同时利用EF Core对JSON字段的支持解析AvailableIn中的单位列表，确保延迟执行特性不受影响：

改造后的完整LINQ代码 #

return from prod in products

    // 保留原有所有关联逻辑
    join price in db.ProductPrices
        on new { prod.StoreId, prod.Sku, PriceSetCode = ctx.PriceSetCode.Value }
        equals new { price.StoreId, price.Sku, price.PriceSetCode } into prices
    from price in prices.DefaultIfEmpty()

    join custPrice in db.ProductCustomerPrices
        on new
        {
            prod.StoreId,
            prod.Sku,
            PriceSetCode = ctx.PriceSetCode.Value,
            AccountId = ctx.AccountId.Value.Value
        }
        equals new
        {
            custPrice.StoreId,
            custPrice.Sku,
            custPrice.PriceSetCode,
            custPrice.AccountId
        } into custPrices
    from custPrice in custPrices.DefaultIfEmpty()

    join custGroupPrice in db.ProductCustGroupPrices
        on new
        {
            prod.StoreId,
            prod.Sku,
            DiscountGroupCode = ctx.DiscountGroupCode,
            PriceSetCode = ctx.PriceSetCode.Value
        }
        equals new
        {
            custGroupPrice.StoreId,
            custGroupPrice.Sku,
            custGroupPrice.DiscountGroupCode,
            custGroupPrice.PriceSetCode
        } into custGroupPrices
    from custGroupPrice in custGroupPrices.DefaultIfEmpty()

    join locationStock in db.ProductLocations
        on new { prod.StoreId, prod.Sku, Id = ctx.LocationId }
        equals new { locationStock.StoreId, locationStock.Sku, locationStock.Id } into locationStocks
    from locationStock in locationStocks.DefaultIfEmpty()

    join lastOrdQty in db.LastOrderQty
        on new { prod.Sku, AccountId = ctx.AccountId.Value.Value }
        equals new { lastOrdQty.Sku, lastOrdQty.AccountId } into lastOrdQtys
    from lastOrdQty in lastOrdQtys.DefaultIfEmpty()

    join memberInfo in db.ProductMemberInfo
        on new { prod.StoreId, prod.Sku, MemberId = memId }
        equals new { memberInfo.StoreId, memberInfo.Sku, memberInfo.MemberId } into memberInfos
    from memberInfo in memberInfos.DefaultIfEmpty()

    join category in db.Categories
        on new { p = prod.PrimaryCategoryId ?? Guid.Empty }
        equals new { p = category.Id } into categoryInfos
    from categoryInfo in categoryInfos.DefaultIfEmpty()

    join substitute in db.ProductSubstitutes
        on new { prod.Sku }
        equals new { substitute.Sku } into prodSubstitute
    from substitute in prodSubstitute.DefaultIfEmpty()

    // 新增：优先按5个字段精准关联ProductIsNotifyMe
    join notifyExact in db.ProductIsNotifyMe
        on new { prod.StoreId, MemberId = memId, prod.Sku, LocationId = ctx.LocationId, prod.Unit }
        equals new { notifyExact.StoreID, notifyExact.MemberID, notifyExact.Sku, notifyExact.LocationId, notifyExact.Unit } into notifyExactMatches
    from notifyExact in notifyExactMatches.DefaultIfEmpty()

    // 新增：降级按4个字段关联ProductIsNotifyMe（忽略Unit）
    join notifyFallback in db.ProductIsNotifyMe
        on new { prod.StoreId, MemberId = memId, prod.Sku, LocationId = ctx.LocationId }
        equals new { notifyFallback.StoreID, notifyFallback.MemberID, notifyFallback.Sku, notifyFallback.LocationId } into notifyFallbackMatches
    from notifyFallback in notifyFallbackMatches.DefaultIfEmpty()

    orderby prod.Sku

    select new ProductQuery
    {
        product = prod,
        price = price,
        custPrice = custPrice,
        categoryInfo = categoryInfo,
        custGroupPrice = custGroupPrice,
        onHand = locationStock.OnHand,
        onHandInf = locationStock.OnHand == null && locationStock.Id != null,
        limit = locationStock.SaleLimit,
        limitInf = locationStock.SaleLimit == null && locationStock.Id != null,
        locenabled = locationStock.Allowed && locationStock.Id != null,
        locsku = locationStock.AlternateSku,
        memberInfo = memberInfo,
        lastOrdQty = lastOrdQty.LastOrderQty,
        lastOrdDate = lastOrdQty.LastOrderTimestamp == null
            ? DateTimeOffset.Now
            : lastOrdQty.LastOrderTimestamp,
        substituteAvail = (substitute != null && !string.IsNullOrEmpty(substitute.ItemSub1)),
        substitutes = substitute != null ? new string[]
            {
                string.IsNullOrEmpty(substitute.ItemSub1) ? string.Empty : substitute.ItemSub1,
                string.IsNullOrEmpty(substitute.ItemSub2) ? string.Empty : substitute.ItemSub2,
                string.IsNullOrEmpty(substitute.ItemSub3) ? string.Empty : substitute.ItemSub3
            } : new string[0],
        // 新增：优先取精准匹配的到货通知记录，无则取降级匹配记录
        NotifyMeRecord = notifyExact ?? notifyFallback,
        // 新增：解析AvailableIn JSON字段，获取产品可用单位列表
        AvailableUnits = prod.AvailableIn != null 
            ? JsonSerializer.Deserialize<List<string>>(prod.AvailableIn) ?? new List<string>()
            : new List<string>()
    };

关键说明 #

• 需确保ProductQuery类已新增NotifyMeRecord（类型为ProductIsNotifyMe）和AvailableUnits（类型为List<string>）属性
• 若使用低版本EF Core，可替换为EF.Functions.JsonValue等方法解析JSON字段，避免客户端评估
• 所有操作均在IQueryable层面构建，完全保留延迟执行特性，仅在枚举查询结果时才会触发数据库调用

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