EFCore通过Include关联清单不存在时返回值为默认值的方式
背景
最近在使用EFCore2.1写代码的时候遇到一个问题，在最终的查询结果中有一个SinglePrice字段查询的结果总是不符合预期，按照自己的设想这个字段是主单的一个TotalFeeAfter字段和主单关联的清单其中的CouponFee合计这两者之间的差值，当然主单关联的清单可能不存在，所以当清单不存在时清单的CouponFee合计值为0,可是在使用EFCore写的代码中结果返回的总是null，显然这个不符合预期，为了交代好这一个过程，我们来看一下这个主清单BO，从而便于自己有一个主观的认知。
1.1 主单保养套餐销售订单（MaintenancePackageOrder）
/// <summary>
/// 保养套餐销售订单
/// </summary>
[Table("MaintenancePackageOrder")]
public class MaintenancePackageOrder : RowVersionAuditEntity, IMustHaveCode {
public MaintenancePackageOrder() {
Coupons = new List<MaintenancePackageOrderCoupon>();
}
/// <summary>
///订单编号
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Code { get; set; }
/// <summary>
///营销分公司编号
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string BranchCode { get; set; }
/// <summary>
///品牌编号
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string BrandCode { get; set; }
/// <summary>
///仓库编号
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string WarehouseCode { get; set; }
/// <summary>
///仓库名称
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_100)]
public string WarehouseName { get; set; }
/// <summary>
///车辆售后档案
/// </summary>
public Guid VehicleSoldId { get; set; }
/// <summary>
///VIN
/// </summary>
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Vin { get; set; }
/// <summary>
///产品编号
/// </summary>
[MaxLength(EntityDefault.FieldLength_50)]
public string ProductCode { get; set; }
/// <summary>
///车牌号
/// </summary>
[MaxLength(EntityDefault.FieldLength_50)]
public string LicensePlate { get; set; }
/// <summary>
///行驶里程
/// </summary>
public int? Mileage { get; set; }
/// <summary>
///优惠后总金额
/// </summary>
public decimal TotalFeeAfter { get; set; }
/// <summary>
///状态
/// </summary>
public MaintenancePackageOrderStatus Status { get; set; }
/// <summary>
///备注
/// </summary>
[MaxLength(EntityDefault.FieldLength_200)]
public string Remark { get; set; }
/// <summary>
/// 产品分类id
/// </summary>
[MaxLength(EntityDefault.FieldLength_50)]
public string ProductCategoryCode { get; set; }
/// <summary>
/// 销售顾问
/// </summary>
[MaxLength(EntityDefault.FieldLength_100)]
public string SaleAdviserName { get; set; }
/// <summary>
/// 保养套餐销售订单抵用券清单（DMSPart）
/// </summary>
public List<MaintenancePackageOrderCoupon> Coupons { get; set; }
}
1.2 清单保养套餐抵用券清单（MaintenancePackageOrderCoupon）
/// <summary>
/// 保养套餐销售订单抵用券清单（DMSPart）
/// </summary>
public class MaintenancePackageOrderCoupon : Entity<Guid> {
/// <summary>
/// 保养套餐销售订单
/// </summary>
[ForeignKeyReference(DeleteBehavior = DeleteBehavior.Cascade)]
public Guid MaintenancePackageOrderId { get; set; }
/// <summary>
/// 代金券
/// </summary>
public Guid? CouponId { get; set; }
/// <summary>
/// 抵用券编号
/// </summary>
[MaxLength(EntityDefault.FieldLength_100)]
public string CouponCode { get; set; }
/// <summary>
/// 抵用券面值
/// </summary>
public decimal? CouponPrice { get; set; }
/// <summary>
/// 抵用金额
/// </summary>
public decimal? CouponFee { get; set; }
}
这里只是为了解释主单MaintenancePackageOrder和清单MaintenancePackageOrderCoupon一对多的关系，所以这里只截取了部分的字段名称，所以可能会和后面的SQL中的部分字段对不上，这里做一个说明。
1.3 EFCore查询数据
public (bool, IQueryable<GetPackageTicketUsageDetailedOutput>) QueryDetailed(GetPackageTicketUsageInput input) {
var isDealer = _companyService.IsDealerUser();
var maintenancePackageOrders = _maintenancePackageOrderRepository.GetAll()
.Include(p => p.Coupons)
.WhereIf(isDealer, p => p.DealerId == SdtSession.TenantId)
.WhereIf(input.BrandId.HasValue, p => p.BrandId == input.BrandId)
.WhereIf(input.BeginSettlementTime.HasValue, p => p.SettlementTime >= input.BeginSettlementTime)
.WhereIf(input.EndSettlementTime.HasValue, p => p.SettlementTime <= input.EndSettlementTime);
var partPrices = _partPriceRepository.GetAll()
.WhereIf(input.MinWholeSalePrice.HasValue, p => p.WholeSalePrice >= input.MinWholeSalePrice)
.WhereIf(input.MaxWholeSalePrice.HasValue, p => p.WholeSalePrice <= input.MaxWholeSalePrice);
var marketingDepartmentDetails = _marketingDepartmentDetailRepository.GetAll()
.WhereIf(input.MarketingDepartmentIds.Any(), m => input.MarketingDepartmentIds.Contains(m.MarketingDepartmentId));
var details = _maintenancePackageOrderTicketDetailRepository.GetAll()
.WhereIf(!string.IsNullOrEmpty(input.PackageTicketCode), p => p.PackageTicketCode.Contains(input.PackageTicketCode))
.WhereIf(input.BeginUsedTime.HasValue, p => p.PackageTicketUsedTime >= input.BeginUsedTime)
.WhereIf(input.EndUsedTime.HasValue, p => p.PackageTicketUsedTime <= input.EndUsedTime);
var result = from maintenancePackageOrder in maintenancePackageOrders
join partPrice in partPrices on maintenancePackageOrder.MaintenancePackageOrderDetail.PartId equals partPrice.PartId
join marketingDepartmentDetail in marketingDepartmentDetails on maintenancePackageOrder.DealerId equals marketingDepartmentDetail.DealerId
join marketingDepartment in _marketingDepartmentRepository.GetAll() on marketingDepartmentDetail.MarketingDepartmentId equals marketingDepartment.Id
join detail in details on maintenancePackageOrder.Id equals detail.MaintenancePackageOrderId
join partPackage in _partPackageRepository.GetAll() on maintenancePackageOrder.MaintenancePackageOrderDetail.PartId equals partPackage.PackagePartId into pp
from partPackage in pp.DefaultIfEmpty()
join maintenancePackageReturnOrder in _maintenancePackageReturnOrderRepository.GetAll()
.Where(p => p.Status == MaintenancePackageReturnOrderStatus.审核通过 || p.Status == MaintenancePackageReturnOrderStatus.已结算)
on maintenancePackageOrder.Id equals maintenancePackageReturnOrder.MaintenancePackageOrderId into mpro
from maintenancePackageReturnOrder in mpro.DefaultIfEmpty()
select new GetPackageTicketUsageDetailedOutput {
BrandCode = maintenancePackageOrder.BrandCode,
BrandName = maintenancePackageOrder.BrandName,
MarketingDepartmentName = marketingDepartment.Name,
LicensePlate = maintenancePackageOrder.LicensePlate,
RetailGuidePrice = partPrice.RetailGuidePrice,
WholeSalePrice = partPrice.WholeSalePrice,
OrderCode = maintenancePackageOrder.Code,
SettlementTime = maintenancePackageOrder.SettlementTime,
CustomerName = maintenancePackageOrder.CustomerName,
CellPhoneNumber = maintenancePackageOrder.CellPhoneNumber,
Vin = maintenancePackageOrder.Vin,
EffectiveTime = maintenancePackageOrder.EffectiveTime,
ExpirationTime = maintenancePackageOrder.ExpirationTime,
//（优惠后金额-代金券金额）÷次数
SinglePrice = (maintenancePackageOrder.TotalFeeAfter - maintenancePackageOrder.Coupons.Sum(d => d.CouponFee ?? 0))
/ (partPackage == null ? 1 : partPackage.PackageTimes),
PackageTicketCode = detail.PackageTicketCode,
PackageTicketStatus = maintenancePackageReturnOrder != null ? PackageTicketStatus.已作废 : detail.UsedFlag ? PackageTicketStatus.已使用 : PackageTicketStatus.未使用
};
result = result.WhereIf(input.PackageTicketStatus.Any(), m => input.PackageTicketStatus.Contains(m.PackageTicketStatus));
return (isDealer, result);
}
在这段EFCore代码中，我们通过前端输入Dto名称为GetPackageTicketUsageInput 来获取最终的输出Dto名称为GetPackageTicketUsageDetailedOutput，中间部分是LINQ语法，这里面重点分析的是SinglePrice的赋值（优惠后金额-代金券金额）/次数 ，按照我们的理解这样的写法是没有问题的，但是结果所有的SinglePrice的值都是null,这里面可以确定的是TotalFeeAfter 是有值的，那么问题到底出在哪里呢？
我们来看看EFCore生成的SQL长什么样子？
select top (20)
[p].[BrandCode],
[p].[BrandName],
[marketingDepartment].[Name] as [MarketingDepartmentName],
[p].[DealerCode],
[p].[DealerName],
[p].[LicensePlate],
[p.MaintenancePackageOrderDetail].[PartCode],
[p.MaintenancePackageOrderDetail].[PartName],
[partPrice].[RetailGuidePrice],
[partPrice].[WholeSalePrice],
[p].[Code] as [OrderCode],
[p].[SettlementTime],
[p].[CustomerName],
[p].[CellPhoneNumber],
[p].[Vin],
[p].[EffectiveTime],
[p].[ExpirationTime],
([p].[TotalFeeAfter] - (
select SUM(COALESCE([d].[CouponFee], 0.0))
from [MaintenancePackageOrderCoupon] as [d]
where [p].[Id] = [d].[MaintenancePackageOrderId]
)) / case
when [partPackage].[Id] is null
then 1 else [partPackage].[PackageTimes]
end as [SinglePrice], [detail].[PackageTicketCode], case
when [t].[Id] is not null
then 3 else CASE
WHEN [detail].[UsedFlag] = 1
then 2 else 1
end
end as [PackageTicketStatus], [detail].[PackageTicketUsedTime], [detail].[SourceBillCode], [detail].[UseDealerCode], [detail].[UseDealerName]
from [MaintenancePackageOrder] as [p]
left join [MaintenancePackageOrderDetail] as [p.MaintenancePackageOrderDetail] on [p].[Id] = [p.MaintenancePackageOrderDetail].[MaintenancePackageOrderId]
inner join [PartPrice] as [partPrice] on [p.MaintenancePackageOrderDetail].[PartId] = [partPrice].[PartId]
inner join [MarketingDepartmentDetail] as [marketingDepartmentDetail] on [p].[DealerId] = [marketingDepartmentDetail].[DealerId]
inner join [MarketingDepartment] as [marketingDepartment] on [marketingDepartmentDetail].[MarketingDepartmentId] = [marketingDepartment].[Id]
inner join [MaintenancePackageOrderTicketDetail] as [detail] on [p].[Id] = [detail].[MaintenancePackageOrderId]
left join [PartPackage] as [partPackage] on [p.MaintenancePackageOrderDetail].[PartId] = [partPackage].[PackagePartId]
left join (
select [p0].*
from [MaintenancePackageReturnOrder] as [p0]
where ([p0].[Status] = 2) or ([p0].[Status] = 3)
) as [t] on [p].[Id] = [t].[MaintenancePackageOrderId]
where [p].[DealerId] ='457EF8B6-C935-4EAC-18F2-08D70733BCF8'
这里面最重要的一段就是下面的部分
([p].[TotalFeeAfter] - (
select SUM(COALESCE([d].[CouponFee], 0.0))
from [MaintenancePackageOrderCoupon] as [d]
where [p].[Id] = [d].[MaintenancePackageOrderId]
)) / case
when [partPackage].[Id] is null
then 1 else [partPackage].[PackageTimes]
end as [SinglePrice]
后面模拟了一下一条记录不存在的情况，如果确实不存在清单MaintenancePackageOrderCoupon，那么CouponFee确实返回的是null
select SUM(COALESCE([d].[CouponFee], 0.0)) as CouponFee
from [MaintenancePackageOrderCoupon] as [d]
where [d].[MaintenancePackageOrderId]='BC594786-B9AD-41AA-8424-00001EB90E37'
那么问题就非常好理解了当当前主单并没有关联清单的话，后面一段返回的是null,所以TotalFeeAfter-null=null,所以无论前面TotalFeeAfter部分是否有值，所以上面出现的问题就可以理解了，显然这个是不符合我们的预期的，我们的预期是如果当前主单并没有关联上清单那么后面一部分返回的值为0而不是返回null,那么实际上就是我们的代码的问题，下面我们就来分析怎么解决这个问题。
解决方案
我们试着来改一下EFCore的写法，既然问题出现在主单不存在清单的情况，那么我们就仅仅来改一下这个部分，这里为了避免重复贴代码，下面只改了核心部分的代码
//（优惠后金额-代金券金额）÷次数
SinglePrice = (maintenancePackageOrder.TotalFeeAfter - (maintenancePackageOrder.Coupons.Any() ? maintenancePackageOrder.Coupons.Sum(d => d.CouponFee ?? 0) : 0))
/ (partPackage == null ? 1 : partPackage.PackageTimes)
在这段代码中只是加了这么一段判断(maintenancePackageOrder.Coupons.Any() ? maintenancePackageOrder.Coupons.Sum(d => d.CouponFee ?? 0) : 0)，这么一改以后我们首先来看看这次生成的SQL到底长成什么样子？
2.1 新生成SQL
select top (20) [p].[BrandCode], [p].[BrandName], [marketingDepartment].[Name] as [MarketingDepartmentName], [p].[DealerCode], [p].[DealerName], [p].[LicensePlate], [p.MaintenancePackageOrderDetail].[PartCode], [p.MaintenancePackageOrderDetail].[PartName], [partPrice].[RetailGuidePrice], [partPrice].[WholeSalePrice], [p].[Code] as [OrderCode], [p].[SettlementTime], [p].[CustomerName], [p].[CellPhoneNumber], [p].[Vin], [p].[EffectiveTime], [p].[ExpirationTime],
([p].[TotalFeeAfter] - case
when (
select case
when exists (
select 1
from [MaintenancePackageOrderCoupon] as [m]
where [p].[Id] = [m].[MaintenancePackageOrderId])
then CAST(1 as bit ) else CAST(0 as bit )
end
) = 1
then (
select sum ( coalesce ([d].[CouponFee], 0.0))
from [MaintenancePackageOrderCoupon] as [d]
where [p].[Id] = [d].[MaintenancePackageOrderId]
) else 0.0
end ) / case
when [partPackage].[Id] is null
then 1 else [partPackage].[PackageTimes]
end as [SinglePrice], [detail].[PackageTicketCode], case
when [t].[Id] is not null
then 3 else case
when [detail].[UsedFlag] = 1
then 2 else 1
end
end as [PackageTicketStatus], [detail].[PackageTicketUsedTime], [detail].[SourceBillCode], [detail].[UseDealerCode], [detail].[UseDealerName]
from [MaintenancePackageOrder] as [p]
left join [MaintenancePackageOrderDetail] as [p.MaintenancePackageOrderDetail] on [p].[Id] = [p.MaintenancePackageOrderDetail].[MaintenancePackageOrderId]
inner join [PartPrice] as [partPrice] on [p.MaintenancePackageOrderDetail].[PartId] = [partPrice].[PartId]
inner join [MarketingDepartmentDetail] as [marketingDepartmentDetail] on [p].[DealerId] = [marketingDepartmentDetail].[DealerId]
inner join [MarketingDepartment] as [marketingDepartment] on [marketingDepartmentDetail].[MarketingDepartmentId] = [marketingDepartment].[Id]
inner join [MaintenancePackageOrderTicketDetail] as [detail] on [p].[Id] = [detail].[MaintenancePackageOrderId]
left join [PartPackage] as [partPackage] on [p.MaintenancePackageOrderDetail].[PartId] = [partPackage].[PackagePartId]
left join (
select [p0].*
from [MaintenancePackageReturnOrder] as [p0]
where ([p0].[Status] = 2) or ([p0].[Status] = 3)
) as [t] on [p].[Id] = [t].[MaintenancePackageOrderId]
where [p].[DealerId] = '457EF8B6-C935-4EAC-18F2-08D70733BCF8'
这次改写后看到差别了吗？这次TotalFeeAfter后面相减的部分不再是简单的去关联一下，而是通过case when操作来分情况进行讨论，其中也和上面一样我把最核心的一部分也拿出来分析。
select case
when (
select case
when exists (
select 1
from [MaintenancePackageOrderCoupon] as [m]
where [m].[MaintenancePackageOrderId]='D2565056-D069-49C6-A8D8-08D7C558D94D')
then CAST(1 as bit ) else CAST(0 as bit )
end
)= 1
then (
select sum (coalesce ([d].[CouponFee], 0.0))
from [MaintenancePackageOrderCoupon] as [d]
where [d].[MaintenancePackageOrderId]='D2565056-D069-49C6-A8D8-08D7C558D94D'
) else 0.0
end as CouponFee;
这次我们也选取了一个主单并没有清单的例子，但是这段SQL最终返回的并不是null,而是返回的默认值0.0,通过这个例子我们就能够完整理解EFCore生成SQL语句之后的一些规律。
总结
最后我们来看看这个问题的关键点：
1 明白在SQL SERVER数据库中任何数据和null进行运算操作最终都将得到null。
2 明白如何通过Include关联清单不存在时返回值为默认值的方式。
3 EFCore代码中分析问题最核心的就是分析最终生成的SQL，通过最终SQL来分析代码是否符合预期，并反过来启发如何修改EFCore代码。
浙公网安备 33010602011771号