詳解EFCore中的導航屬性

Posted on by

　　使用瞭這麼久的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);
        }
    }

　　4 IncludeFilter用法

　　在有些場景中我們可能需要帶出清單的時候並且過濾清單，這個功能算是對Include方法的一個提升，可以將兩個操作合並到一起來進行，這個在使用的時候需要註意 這個並不是Asp.Net Core自帶的功能，這個需要通過引入包 Z.EntityFramework.Plus.EFCore.dll的包來實現的，如果你們的系統中使用的是ABP作為項目主框架，那麼你隻需要引用 Abp.EntityFrameworkCore.EFPlus這個包就可以瞭，因為這個包中就包含和Z.EntityFramework相關的子包，這個在使用的時候需要註意。

 　　下面我們來看一看我們的代碼中是怎麼使用的。

private (Company company, IEnumerable<PartSaleOrderType> partSaleOrderTypes) GetCompanyDetailForFactory(Guid id) {
            var currentPartSaleOrderTypes = GetCurrentPartSaleOrderTypes();
            var currentPartSaleOrderTypeIds = currentPartSaleOrderTypes.Select(t => t.Id);
            var company = _companyRepository.GetAll()
                .IncludeFilter(c => c.CustomerPartInformations.Where(i => i.BranchId == SdtSession.TenantId.GetValueOrDefault()))
                .IncludeFilter(c => c.CustomerOrderWarehouses.Where(w => currentPartSaleOrderTypeIds.Contains(w.PartSaleOrderTypeId)))
                .IncludeFilter(c => c.CustomerMarkupRates.Where(w => currentPartSaleOrderTypeIds.Contains(w.PartSaleOrderTypeId)))
                .IncludeFilter(c => c.OrderShippingSequences.Where(w => currentPartSaleOrderTypeIds.Contains(w.PartSaleOrderTypeId)))
                .IncludeFilter(c => c.OrderingCalendars.Where(w => currentPartSaleOrderTypeIds.Contains(w.PartSaleOrderTypeId)))
                .FirstOrDefault(d => d.Id == id && d.Status == BaseDataStatus.生效);
            if (company == null) {
                throw new EntityNotFoundException(SharedLocalizer["未能找到對應的企業"]);
            }
            return (company, currentPartSaleOrderTypes);
        }

　　這個提供瞭一種新的清單過濾方式，不僅提高瞭效率而且使代碼更加優化簡練。

　　5 特殊情況

　　這裡還介紹一種不通過Include方法來獲取清單中的方式，就像下面這種寫法，Company對象和OrderingCalendars之間建立瞭一對多的導航屬性，我們在使用 company.OrderingCalendars之前先將內部的清查查詢出來（不需要定義變量接收，使用_即可），這樣也是能通過導航屬性自動映射到company的OrderingCalendars中去的，這個在使用的時候需要特別註意，查詢的時候要提前ToList，將數據查詢到內存裡面

_ = _orderingCalendarRepository.GetAll().Where(t => t.OrderingCompanyId == company.Id).ToList();           
           var tempCalendars = company.OrderingCalendars.OrderBy(d => d.PartSaleOrderType.Level).Select(d => new {
               d.PartSaleOrderType.BrandId,
               d.PartSaleOrderType.BrandCode,
               d.PartSaleOrderType.BrandName,
               d.PartSaleOrderTypeId,
               d.PartSaleOrderTypeCode,
               d.PartSaleOrderTypeName,
               d.Year,
               d.Month,
               d.Day
           });

以上就是詳解EFCore中的導航屬性的詳細內容，更多關於EFCore 導航屬性的資料請關註WalkonNet其它相關文章！

推薦閱讀：