詳解EFCore中的導(dǎo)航屬性

更新時間：2021年03月11日 10:06:23 作者：Hello 尋夢者！

這篇文章主要介紹了詳解EFCore中的導(dǎo)航屬性的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)使用ASP.NET CORE，感興趣的朋友可以了解下

　　使用了這么久的EntityFrameworkCore框架，今天想來就其中的一個部分來做一個知識的梳理，從而使自己對于整個知識有一個更加深入的理解，如果你對EFCore中的實體關(guān)系不熟悉你需要有一個知識的預(yù)熱，這樣你才能夠更好的去理解整個知識,在建立好了這些實體之間的關(guān)系以后，我們可以通過使用InClude、ThenInclude這些方法來進(jìn)行快速獲得對應(yīng)關(guān)聯(lián)實體數(shù)據(jù)，用起來確實十分的方便，這里我們將通過一系列的例子來進(jìn)行說明。

　 1 單獨使用Include

　　在介紹這個方法之前，我來先貼出實體之間的關(guān)聯(lián)關(guān)系，假設(shè)這里有三個相互關(guān)聯(lián)的實體VehicleWarranty、WarrantyWarningLevel、VehicleWarrantyRepairHistory這三個實體后面兩個都是第一個的子級并且并且VehicleWarranty、WarrantyWarningLevel之間的關(guān)系是1對1的關(guān)系，VehicleWarranty和VehicleWarrantyRepairHistory之間是1：N的關(guān)系，即1對多的關(guān)系，我們這里貼出具體的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; }
 
        //產(chǎn)品分類
        public Guid? ProductCategoryId { get; set; }
 
        //產(chǎn)品分類編號
        [MaxLength(EntityDefault.FieldLength_50)]
        public string ProductCategoryCode { get; set; }
 
        //產(chǎn)品分類名稱
        [MaxLength(EntityDefault.FieldLength_100)]
        public string ProductCategoryName { get; set; }
 
        //車牌號
        [MaxLength(EntityDefault.FieldLength_50)]
        public string LicensePlate { get; set; }
 
        //發(fā)動機號
        [MaxLength(EntityDefault.FieldLength_50)]
        public string EngineCode { get; set; }
 
        //變速箱號
        [MaxLength(EntityDefault.FieldLength_50)]
        public string TransmissionSn { get; set; }
 
        //開發(fā)票日期
        public DateTime InvoiceDate { get; set; }
 
        //行駛里程
        public int Mileage { get; set; }
 
        //是否三包期內(nèi)
        public bool? IsInWarranty { get; set; }
 
        //預(yù)警等級
        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; }
 
        //預(yù)警內(nèi)容
        [MaxLength(EntityDefault.FieldLength_800)]
        public string WarningComment { get; set; }
 
        //累計維修天數(shù)
        public int TotoalRepairDays { get; set; }
 
        //售出后60天/3000KM內(nèi)嚴(yán)重故障次數(shù)
        public int? FNum { get; set; }
 
        //嚴(yán)重安全性能故障累計次數(shù)
        public int? GNum { get; set; }
 
        //發(fā)動機總成累計更換次數(shù)
        public int? HNum { get; set; }
 
        //變速箱總成累計更換次數(shù)
        public int? INum { get; set; }
 
        //發(fā)動機主要零件最大更換次數(shù)
        public int? JNum { get; set; }
 
        //變速箱主要零件最大更換次數(shù)
        public int? KNum { get; set; }
 
        //同一主要零件最大更換次數(shù)
        public int? LNum { get; set; }
 
        //同一產(chǎn)品質(zhì)量問題最大累計次數(shù)（部件+故障+方位）
        public int? MNum { get; set; }
 
        //同一產(chǎn)品質(zhì)量問題最大累計次數(shù)
        public int? NNum { get; set; }
 
        public List<VehicleWarrantyRepairHistory> Details { get; set; }
    }
 
/// <summary>
    /// 三包預(yù)警等級（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; }
 
        //經(jīng)銷商
        public Guid DealerId { get; set; }
 
        //經(jīng)銷商編號
        [Required]
        [MaxLength(EntityDefault.FieldLength_50)]
        public string DealerCode { get; set; }
 
        //經(jīng)銷商名稱
        [Required]
        [MaxLength(EntityDefault.FieldLength_100)]
        public string DealerName { get; set; }
 
        //履歷來源
        public VehicleWarrantyRepairHistorySource Source { get; set; }
 
        //累計維修天數(shù)
        public int? TotoalRepairDays { get; set; }
 
        //售出后60天/3000KM內(nèi)嚴(yán)重故障次數(shù)
        public int? FNum { get; set; }
 
        //嚴(yán)重安全性能故障累計次數(shù)
        public int? GNum { get; set; }
 
        //發(fā)動機總成累計更換次數(shù)
        public int? HNum { get; set; }
 
        //變速箱總成累計更換次數(shù)
        public int? INum { get; set; }
 
        //發(fā)動機主要零件最大更換次數(shù)
        public int? JNum { get; set; }
 
        //變速箱主要零件最大更換次數(shù)
        public int? KNum { get; set; }
 
        //同一主要零件最大更換次數(shù)
        public int? LNum { get; set; }
 
        //同一產(chǎn)品質(zhì)量問題最大累計次數(shù)（部件+故障+方位）
        public int? MNum { get; set; }
 
        //同一產(chǎn)品質(zhì)量問題最大累計次數(shù)
        public int? NNum { get; set; }
    }

　　這里我們貼出第一個簡單的查詢示例，通過Include方法來一下子查詢出關(guān)聯(lián)的三包預(yù)警等級這個實體，在我們的例子中我們返回的結(jié)果是帶分頁的，而且會根據(jù)前端傳遞的Dto來進(jìn)行過濾，這里我們來看這段代碼怎么實體。

/// <summary>
        /// 查詢車輛三包信息
        /// </summary>
        /// <param name="input">查詢輸入</param>
        /// <param name="pageRequest">分頁請求</param>
        /// <returns>帶分頁的三包預(yù)警車輛信息</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就能夠查詢出關(guān)聯(lián)的實體，為什么能夠?qū)崿F(xiàn)，那是因為在VehicleWarranty實體中存在WarrantyWarningLevel實體的外鍵，并且這里還增加了外鍵關(guān)聯(lián)的實體，這樣才能夠正確使用InClude方法，并且這個InClude方法只能夠以實體作為參數(shù)，不能以外鍵作為參數(shù)，到了這里我想提出一個問題，這里最終生成的SQL（SqlServer數(shù)據(jù)庫）是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之間的關(guān)系的話，遷移到數(shù)據(jù)庫會默認(rèn)生成外鍵約束，這個在使用的時候需要特別注意，但是如果你只是添加了外鍵而沒有添加對應(yīng)的外鍵同名的實體是不會生成外鍵約束關(guān)系的，這個暫時不理解里面的實現(xiàn)機制。

　　2 主清單使用Include

　　剛才介紹的是1對1的關(guān)聯(lián)關(guān)系，那么像VehicleWarranty、VehicleWarrantyRepairHistory之間有明顯的主清單關(guān)系，即一個VehicleWarranty對應(yīng)多個VehicleWarrantyRepairHistory的時候使用InClude方法會生成什么樣的SQL語句呢？這里我也貼出代碼，然后再來分析生成的SQL語句。

/// <summary>
        /// 查詢特定的三包預(yù)警車輛信息
        /// </summary>
        /// <param name="id">特定Id</param>
        /// <returns>特定的三包預(yù)警車輛信息</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("找不到當(dāng)前特定的三包預(yù)警車輛信息");
            }
 
            var retResult = ObjectMapper.Map<GetVehicleWarrantyWithDetailsOutput>(query);
            return retResult;
        }

　　這里使用了兩個InClude方法，那么EFCore會怎么生成這個SQL呢？通過查詢最終的SQL我們發(fā)現(xiàn)EFCore在處理這類問題的時候是分開進(jìn)行查詢，然后再合并到查詢的實體中去的，所以在這個查詢的過程中生成的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查詢并且會將前面查詢的結(jié)果作為后面查詢的部分條件來進(jìn)行了，待整個查詢完畢后再在內(nèi)存中將這些結(jié)果組合到一個query對象中。

　　3 ThenInclude用法

　　上面的介紹完了之后，你應(yīng)該能夠明白這個Include的具體含義和用法了，接著上面的例子，如果WarrantyWarningLevel里面還有通過外鍵Id去關(guān)聯(lián)別的實體，這個時候ThenInclude就派上了用場了，理論上只要彼此之間建立了這種外鍵關(guān)系就可以一直ThenInClude下去，但是一般情況下不會用到這么復(fù)雜的情況，當(dāng)然這里面每一個Include也都是作為一個單獨的查詢來進(jìn)行的，這個也可以找具體的例子進(jìn)行試驗，這里也貼出一個具體的例子吧。

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["當(dāng)前維修合同不存在"]);
            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方法只能一層層向下進(jìn)行，如果我想對同一個實體里面的兩個關(guān)聯(lián)實體做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同一個實體兩次，然后分別調(diào)用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方法的一個提升，可以將兩個操作合并到一起來進(jìn)行，這個在使用的時候需要注意這個并不是Asp.Net Core自帶的功能，這個需要通過引入包 Z.EntityFramework.Plus.EFCore.dll的包來實現(xiàn)的，如果你們的系統(tǒng)中使用的是ABP作為項目主框架，那么你只需要引用 Abp.EntityFrameworkCore.EFPlus這個包就可以了，因為這個包中就包含和Z.EntityFramework相關(guān)的子包，這個在使用的時候需要注意。

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

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["未能找到對應(yīng)的企業(yè)"]);
            }
            return (company, currentPartSaleOrderTypes);
        }

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

　　5 特殊情況

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

_ = _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中的導(dǎo)航屬性的詳細(xì)內(nèi)容，更多關(guān)于EFCore 導(dǎo)航屬性的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: