EFCore中的导航属性

　　使用了这么久的EntityFrameworkCore框架，今天想来就其中的一个部分来做一个知识的梳理，从而使自己对于整个知识有一个更加深入的理解，如果你对EFCore中的实体关系不熟悉你需要有一个知识的预热，这样你才能够更好的去理解整个知识,在建立好了这些实体之间的关系以后，我们可以通过使用InClude、ThenInclude这些方法来进行快速获得对应关联实体数据，用起来确实十分的方便，这里我们将通过一系列的例子来进行说明。

　 1 单独使用Include

　　在介绍这个方法之前，我来先贴出实体之间的关联关系，假设这里有三个相互关联的实体VehicleWarranty、WarrantyWarningLevel、VehicleWarrantyRepairHistory这三个实体后面两个都是第一个的子级并且并且VehicleWarranty、WarrantyWarningLevel之间的关系是1对1的关系，VehicleWarranty和VehicleWarrantyRepairHistory之间是1：N的关系，即1对多的关系，我们这里贴出具体的Model，从而方便后面分析具体的代码。

    /// <summary>

    /// 车辆三包信息（DCSService）

    /// </summary>

    public class VehicleWarranty : Entity<Guid> {

        public VehicleWarranty() {

            Details = new List<VehicleWarrantyRepairHistory>();

        }

        //车辆售后档案

        public Guid VehicleSoldId { get; set; }

        //VIN

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string Vin { get; set; }

        //产品分类

        public Guid? ProductCategoryId { get; set; }

        //产品分类编号

        [MaxLength(EntityDefault.FieldLength_50)]

        public string ProductCategoryCode { get; set; }

        //产品分类名称

        [MaxLength(EntityDefault.FieldLength_100)]

        public string ProductCategoryName { get; set; }

        //车牌号

        [MaxLength(EntityDefault.FieldLength_50)]

        public string LicensePlate { get; set; }

        //发动机号

        [MaxLength(EntityDefault.FieldLength_50)]

        public string EngineCode { get; set; }

        //变速箱号

        [MaxLength(EntityDefault.FieldLength_50)]

        public string TransmissionSn { get; set; }

        //开发票日期

        public DateTime InvoiceDate { get; set; }

        //行驶里程

        public int Mileage { get; set; }

        //是否三包期内

        public bool? IsInWarranty { get; set; }

        //预警等级

        public Guid? WarningLevelId { get; set; }

        public WarrantyWarningLevel WarningLevel { get; set; }

        //等级编号

        [MaxLength(EntityDefault.FieldLength_50)]

        public string LevelCode { get; set; }

        //等级名称

        [MaxLength(EntityDefault.FieldLength_100)]

        public string LevelName { get; set; }

        //预警内容

        [MaxLength(EntityDefault.FieldLength_800)]

        public string WarningComment { get; set; }

        //累计维修天数

        public int TotoalRepairDays { get; set; }

        //售出后60天/3000KM内严重故障次数

        public int? FNum { get; set; }

        //严重安全性能故障累计次数

        public int? GNum { get; set; }

        //发动机总成累计更换次数

        public int? HNum { get; set; }

        //变速箱总成累计更换次数

        public int? INum { get; set; }

        //发动机主要零件最大更换次数

        public int? JNum { get; set; }

        //变速箱主要零件最大更换次数

        public int? KNum { get; set; }

        //同一主要零件最大更换次数

        public int? LNum { get; set; }

        //同一产品质量问题最大累计次数（部件+故障+方位）

        public int? MNum { get; set; }

        //同一产品质量问题最大累计次数

        public int? NNum { get; set; }

        public List<VehicleWarrantyRepairHistory> Details { get; set; }

    }

/// <summary>

    /// 三包预警等级（DCS）

    /// </summary>

    public class WarrantyWarningLevel : Entity<Guid> {

        //等级编号

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string Code { get; set; }

        //等级名称

        [Required]

        [MaxLength(EntityDefault.FieldLength_100)]

        public string Name { get; set; }

        //颜色

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string Color { get; set; }

        //备注

        [MaxLength(EntityDefault.FieldLength_200)]

        public string Remark { get; set; }

    }

    /// <summary>

    /// 车辆三包信息维修履历（DCSService）

    /// </summary>

    public class VehicleWarrantyRepairHistory : Entity<Guid> {

        //车辆三包信息

        [Required]

        public Guid VehicleWarrantyId { get; set; }

        public VehicleWarranty VehicleWarranty { get; set; }

        //VIN

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string Vin { get; set; }

        //维修合同

        public Guid RepairContractId { get; set; }

        //维修合同编号

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string RepairContractCode { get; set; }

        //处理时间

        public DateTime? DealTime { get; set; }

        //经销商

        public Guid DealerId { get; set; }

        //经销商编号

        [Required]

        [MaxLength(EntityDefault.FieldLength_50)]

        public string DealerCode { get; set; }

        //经销商名称

        [Required]

        [MaxLength(EntityDefault.FieldLength_100)]

        public string DealerName { get; set; }

        //履历来源

        public VehicleWarrantyRepairHistorySource Source { get; set; }

        //累计维修天数

        public int? TotoalRepairDays { get; set; }

        //售出后60天/3000KM内严重故障次数

        public int? FNum { get; set; }

        //严重安全性能故障累计次数

        public int? GNum { get; set; }

        //发动机总成累计更换次数

        public int? HNum { get; set; }

        //变速箱总成累计更换次数

        public int? INum { get; set; }

        //发动机主要零件最大更换次数

        public int? JNum { get; set; }

        //变速箱主要零件最大更换次数

        public int? KNum { get; set; }

        //同一主要零件最大更换次数

        public int? LNum { get; set; }

        //同一产品质量问题最大累计次数（部件+故障+方位）

        public int? MNum { get; set; }

        //同一产品质量问题最大累计次数

        public int? NNum { get; set; }

    }

　　这里我们贴出第一个简单的查询示例，通过Include方法来一下子查询出关联的三包预警等级这个实体，在我们的例子中我们返回的结果是带分页的，而且会根据前端传递的Dto来进行过滤，这里我们来看这段代码怎么实体。

/// <summary>

        /// 查询车辆三包信息

        /// </summary>

        /// <param name="input">查询输入</param>

        /// <param name="pageRequest">分页请求</param>

        /// <returns>带分页的三包预警车辆信息</returns>

        public async Task<Page<GetVehicleWarrantiesOutput>> GetVehicleWarrantiesAsync(GetVehicleWarrantiesInput input, PageRequest pageRequest) {

            var queryResults = _vehicleWarrantyRepository.GetAll()

                .Include(v => v.WarningLevel)

                .Where(v => _vehicleSoldRepository.GetAll().Any(vs => vs.Status == VehicleStatus.实销完成 && v.Vin == vs.Vin));

            var totalCount = await queryResults.CountAsync();

            var pagedResults = await queryResults.ProjectTo<GetVehicleWarrantiesOutput>(_autoMapper.ConfigurationProvider).PageAndOrderBy(pageRequest).ToListAsync();

            return new Page<GetVehicleWarrantiesOutput>(pageRequest, totalCount, pagedResults);

        }

　　在这里我们看到了通过一个Include就能够查询出关联的实体，为什么能够实现，那是因为在VehicleWarranty实体中存在WarrantyWarningLevel实体的外键，并且这里还增加了外键关联的实体，这样才能够正确使用InClude方法，并且这个InClude方法只能够以实体作为参数，不能以外键作为参数，到了这里我想提出一个问题，这里最终生成的SQL（SqlServer数据库）是left join 还是inner join呢？在看完后面的分析之前需要思考一下。

select top (20)

  [v].[EngineCode],

  [v].[GNum],

  [v].[Id],

  [v.WarningLevel].[Color] as [LevelColor],

  [v].[LevelName],

  [v].[LicensePlate],

  [v].[ProductCategoryName],

  [v].[TotoalRepairDays],

  [v].[Vin]

from [VehicleWarranty] as [v]

  left join [WarrantyWarningLevel] as [v.WarningLevel] on [v].[WarningLevelId] = [v.WarningLevel].[Id]

where EXISTS(

    select 1

    from [VehicleSold] as [vs]

    where ([vs].[Status] = 7) and ([v].[Vin] = [vs].[Vin]))

order by [v].[Vin]

　　这里我们看到生成的SQL语句是left join ,那么这里为什么不是inner join呢？这里先给你看具体的答案吧？这里你看懂了吗？问题就处在我这里建立的外键是可为空的 public Guid? WarningLevelId { get; set; }、如果是不可为空的外键那么生成的SQL就是inner join这个你可以亲自尝试。另外有一个需要提醒的就是，如果你像上面的实体中建立了VehicleWarranty、WarrantyWarningLeve之间的关系的话，迁移到数据库会默认生成外键约束，这个在使用的时候需要特别注意，但是如果你只是添加了外键而没有添加对应的外键同名的实体是不会生成外键约束关系的，这个暂时不理解里面的实现机制。

　　2 主清单使用Include

　　刚才介绍的是1对1的关联关系，那么像VehicleWarranty、VehicleWarrantyRepairHistory之间有明显的主清单关系，即一个VehicleWarranty对应多个VehicleWarrantyRepairHistory的时候使用InClude方法会生成什么样的SQL语句呢？这里我也贴出代码，然后再来分析生成的SQL语句。　　

/// <summary>

        /// 查询特定的三包预警车辆信息

        /// </summary>

        /// <param name="id">特定Id</param>

        /// <returns>特定的三包预警车辆信息</returns>

        public async Task<GetVehicleWarrantyWithDetailsOutput> GetVehicleWarrantyWithDetailsAsync(Guid id) {

            var query = await _vehicleWarrantyRepository.GetAll()

                .Include(v => v.WarningLevel)

                .Include(v => v.Details)

                .SingleOrDefaultAsync(v => v.Id == id);

            if (null == query) {

                throw new ValidationException("找不到当前特定的三包预警车辆信息");

            }

            var retResult = ObjectMapper.Map<GetVehicleWarrantyWithDetailsOutput>(query);

            return retResult;

        }

　　这里使用了两个InClude方法，那么EFCore会怎么生成这个SQL呢？通过查询最终的SQL我们发现EFCore在处理这类问题的时候是分开进行查询，然后再合并到查询的实体中去的，所以在这个查询的过程中生成的SQL如下：

select top (2)

  [v].[Id],

  [v].[EngineCode],

  [v].[FNum],

  [v].[GNum],

  [v].[HNum],

  [v].[INum],

  [v].[InvoiceDate],

  [v].[IsInWarranty],

  [v].[JNum],

  [v].[KNum],

  [v].[LNum],

  [v].[LevelCode],

  [v].[LevelName],

  [v].[LicensePlate],

  [v].[MNum],

  [v].[Mileage],

  [v].[NNum],

  [v].[ProductCategoryCode],

  [v].[ProductCategoryId],

  [v].[ProductCategoryName],

  [v].[TotoalRepairDays],

  [v].[TransmissionSn],

  [v].[VehicleSoldId],

  [v].[Vin],

  [v].[WarningComment],

  [v].[WarningLevelId],

  [v.WarningLevel].[Id],

  [v.WarningLevel].[Code],

  [v.WarningLevel].[Color],

  [v.WarningLevel].[Name],

  [v.WarningLevel].[Remark]

from [VehicleWarranty] as [v]

  left join [WarrantyWarningLevel] as [v.WarningLevel] on [v].[WarningLevelId] = [v.WarningLevel].[Id]

where [v].[Id] = @__id_0

order by [v].[Id]

select

  [v.Details].[Id],

  [v.Details].[DealTime],

  [v.Details].[DealerCode],

  [v.Details].[DealerId],

  [v.Details].[DealerName],

  [v.Details].[FNum],

  [v.Details].[GNum],

  [v.Details].[HNum],

  [v.Details].[INum],

  [v.Details].[JNum],

  [v.Details].[KNum],

  [v.Details].[LNum],

  [v.Details].[MNum],

  [v.Details].[NNum],

  [v.Details].[RepairContractCode],

  [v.Details].[RepairContractId],

  [v.Details].[Source],

  [v.Details].[TotoalRepairDays],

  [v.Details].[VehicleWarrantyId],

  [v.Details].[Vin]

from [VehicleWarrantyRepairHistory] as [v.Details]

  inner join (

               select distinct [t].*

               from (

                      select top (1) [v0].[Id]

                      from [VehicleWarranty] as [v0]

                        left join [WarrantyWarningLevel] as [v.WarningLevel0]

                          on [v0].[WarningLevelId] = [v.WarningLevel0].[Id]

                      where [v0].[Id] = @__id_0

                      order by [v0].[Id]

                    ) as [t]

             ) as [t0] on [v.Details].[VehicleWarrantyId] = [t0].[Id]

order by [t0].[Id]

　　这个在查询的过程中会分作几个SQL查询并且会将前面查询的结果作为后面查询的部分条件来进行了，待整个查询完毕后再在内存中将这些结果组合到一个query对象中。

　　3 ThenInclude用法

　　上面的介绍完了之后，你应该能够明白这个Include的具体含义和用法了，接着上面的例子，如果WarrantyWarningLevel里面还有通过外键Id去关联别的实体，这个时候ThenInclude就派上了用场了，理论上只要彼此之间建立了这种外键关系就可以一直ThenInClude下去，但是一般情况下不会用到这么复杂的情况，当然这里面每一个Include也都是作为一个单独的查询来进行的，这个也可以找具体的例子进行试验，这里也贴出一个具体的例子吧。

public async Task<GetRepairContractDetailForSettlementOutput> GetById(Guid id) {

            var repairContract = await _repairContractRepository.GetAll()

                .Include(d => d.RepairContractWorkItems)

                    .ThenInclude(w => w.Materials)

                .FirstOrDefaultAsync(r => r.Id == id);

            if (repairContract == null)

                throw new ValidationException(_localizer["当前维修合同不存在"]);

            var vehicleSold = _vehicleSoldRepository.Get(repairContract.VehicleId);

            var isTrafficSubsidy = _repairContractManager.IsTrafficSubsidy(repairContract.Id);

            var (nextMaintenanceMileage, nextMaintenanceTime) = _repairContractManager.GetNextMaintainInfo(repairContract, vehicleSold);

            var result = new GetRepairContractDetailForSettlementOutput() {

                Id = repairContract.Id,

                Code = repairContract.Code,

                CustomerName = repairContract.CustomerName,

                CellPhoneNumber = repairContract.CellPhoneNumber,

                Vin = repairContract.Vin,

                LicensePlate = repairContract.LicensePlate,

                NextMaintenanceTime = nextMaintenanceTime,

                NextMaintenanceMileage = nextMaintenanceMileage,

                LaborFee = repairContract.LaborFee,

                LaborFeeAfter = repairContract.LaborFeeAfter,

                MaterialFee = repairContract.MaterialFee,

                MaterialFeeAfter = repairContract.MaterialFeeAfter,

                OutFee = repairContract.OutFee,

                OtherFee = repairContract.OtherFee,

                TotalFeeAfter = repairContract.TotalFeeAfter,

                ShowIsTrafficSubsidy = isTrafficSubsidy,

                LastMaintenanceTime = vehicleSold.LastMaintenanceTime,

                LastMaintenanceMileage = vehicleSold.LastMaintenanceMileage,

                WorkItems = _mapper.Map<IList<GetRepairContractWorkItemForSettlementOutput>>(repairContract.RepairContractWorkItems)

            };

            return result;

        }

　　最后还要介绍一种极特殊的情况，由于ThenInclude方法只能一层层向下进行，如果我想对同一个实体里面的两个关联实体做ThenInclude操作这个怎么处理，这里就直接给出代码吧。

/// <summary>

        ///维修合同完成的事件

        /// </summary>

        /// <param name="repairContractId"></param>

        public void Finished(Guid repairContractId) {

            var repairContract = _repairContractRepository.GetAll()

                .Include(c => c.RepairContractWorkItems).ThenInclude(wi => wi.Materials)

                .Include(c => c.RepairContractWorkItems).ThenInclude(wi => wi.Fault)

                .SingleOrDefault(c => c.Id == repairContractId);

            var repairContractAdjusts = _repairContractAdjustRepository.GetAll()

                .Include(a => a.WorkItems).ThenInclude(w => w.Materials)

                .Where(a => a.RepairContractId == repairContractId).ToList();

            var @event = new AddRepairContractEvent {

                Key = repairContract?.Code,

                RepairContract = repairContract,

                RepairContractAdjusts = repairContractAdjusts

            };

            _producer.Produce(@event);

        }

　　这里需要Include同一个实体两次，然后分别调用ThenInclude方法，这个属于比较特殊的情况，在使用的时候需要注意。

　　温馨提示：

　　这里读者在看代码的时候可能不太理解类似这种 _repairContractRepository的具体由来，这里贴出一份完整的代码。

internal class AddRepairContractEventManager : DomainService, IAddRepairContractEventManager {

        private readonly KafkaProducer _producer;

        private readonly IRepository<RepairContract, Guid> _repairContractRepository;

        private readonly IRepository<RepairContractAdjust, Guid> _repairContractAdjustRepository;

        public AddRepairContractEventManager(KafkaProducer producer,

                                          IRepository<RepairContract, Guid> repairContractRepository,

                                          IRepository<RepairContractAdjust, Guid> repairContractAdjustRepository) {

            _producer = producer;

            _repairContractRepository = repairContractRepository;

            _repairContractAdjustRepository = repairContractAdjustRepository;

        }

        /// <summary>

        ///维修合同完成的事件

        /// </summary>

        /// <param name="repairContractId"></param>

        public void Finished(Guid repairContractId) {

            var repairContract = _repairContractRepository.GetAll()

                .Include(c => c.RepairContractWorkItems).ThenInclude(wi => wi.Materials)

                .Include(c => c.RepairContractWorkItems).ThenInclude(wi => wi.Fault)

                .SingleOrDefault(c => c.Id == repairContractId);

            var repairContractAdjusts = _repairContractAdjustRepository.GetAll()

                .Include(a => a.WorkItems).ThenInclude(w => w.Materials)

                .Where(a => a.RepairContractId == repairContractId).ToList();

            var @event = new AddRepairContractEvent {

                Key = repairContract?.Code,

                RepairContract = repairContract,

                RepairContractAdjusts = repairContractAdjusts

            };

            _producer.Produce(@event);

        }

    }

巴特西

EFCore中的导航属性

最新文章

热门文章