Explain the navigation attributes in EFCore in detail
- 2021-11-29 23:31:43
- OfStack
With the EntityFrameworkCore framework in use for so long, Today, I want to sort out one part of it. So as to have a deeper understanding of the whole knowledge, If you are not familiar with the entity relationship in EFCore, you need to have a warm-up of knowledge. In this way, you can better understand the whole knowledge. After establishing the relationship between these entities, we can quickly obtain the corresponding associated entity data by using InClude and ThenInclude, which is really 10 points convenient to use. Here we will illustrate it through a series of examples.
1 Use Include alone
Before introducing this method, Let me post the relationship between entities first, assuming that there are three interrelated entities VehicleWarranty, WarrantyWarningLevel and VehicleWarrantyRepairHistory, the last two of which are children of the first one, and the relationship between VehicleWarranty and WarrantyWarningLevel is 1-to-1, and the relationship between VehicleWarranty and VehicleWarrantyRepairHistory is 1: N, that is, 1-to-many. We post specific Model here, which is convenient for analyzing specific codes later.
/// <summary>
/// Vehicles 3 Package information ( DCSService )
/// </summary>
public class VehicleWarranty : Entity<Guid> {
public VehicleWarranty() {
Details = new List<VehicleWarrantyRepairHistory>();
}
// Vehicle after-sales files
public Guid VehicleSoldId { get; set; }
//VIN
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Vin { get; set; }
// Product classification
public Guid? ProductCategoryId { get; set; }
// Product classification number
[MaxLength(EntityDefault.FieldLength_50)]
public string ProductCategoryCode { get; set; }
// Product classification name
[MaxLength(EntityDefault.FieldLength_100)]
public string ProductCategoryName { get; set; }
// License plate number
[MaxLength(EntityDefault.FieldLength_50)]
public string LicensePlate { get; set; }
// Engine number
[MaxLength(EntityDefault.FieldLength_50)]
public string EngineCode { get; set; }
// Gearbox number
[MaxLength(EntityDefault.FieldLength_50)]
public string TransmissionSn { get; set; }
// Invoicing date
public DateTime InvoiceDate { get; set; }
// Mileage
public int Mileage { get; set; }
// Whether or not 3 During the contract period
public bool? IsInWarranty { get; set; }
// Early warning level
public Guid? WarningLevelId { get; set; }
public WarrantyWarningLevel WarningLevel { get; set; }
// Grade number
[MaxLength(EntityDefault.FieldLength_50)]
public string LevelCode { get; set; }
// Grade name
[MaxLength(EntityDefault.FieldLength_100)]
public string LevelName { get; set; }
// Early warning content
[MaxLength(EntityDefault.FieldLength_800)]
public string WarningComment { get; set; }
// Cumulative maintenance days
public int TotoalRepairDays { get; set; }
// After sale 60 Days /3000KM Number of serious failures in
public int? FNum { get; set; }
// Cumulative number of serious safety performance failures
public int? GNum { get; set; }
// Cumulative replacement times of engine assembly
public int? HNum { get; set; }
// Cumulative replacement times of gearbox assembly
public int? INum { get; set; }
// Maximum replacement times of main engine parts
public int? JNum { get; set; }
// Maximum replacement times of main parts of gearbox
public int? KNum { get; set; }
// Same as 1 Maximum replacement times of main parts
public int? LNum { get; set; }
// Same as 1 Maximum cumulative number of product quality problems (parts + Malfunction + Azimuth)
public int? MNum { get; set; }
// Same as 1 Maximum cumulative number of product quality problems
public int? NNum { get; set; }
public List<VehicleWarrantyRepairHistory> Details { get; set; }
}
/// <summary>
/// 3 Package alert level ( DCS )
/// </summary>
public class WarrantyWarningLevel : Entity<Guid> {
// Grade number
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Code { get; set; }
// Grade name
[Required]
[MaxLength(EntityDefault.FieldLength_100)]
public string Name { get; set; }
// Color
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Color { get; set; }
// Remarks
[MaxLength(EntityDefault.FieldLength_200)]
public string Remark { get; set; }
}
/// <summary>
/// Vehicles 3 Package information maintenance resume ( DCSService )
/// </summary>
public class VehicleWarrantyRepairHistory : Entity<Guid> {
// Vehicles 3 Package information
[Required]
public Guid VehicleWarrantyId { get; set; }
public VehicleWarranty VehicleWarranty { get; set; }
//VIN
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string Vin { get; set; }
// Maintenance contract
public Guid RepairContractId { get; set; }
// Maintenance contract number
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string RepairContractCode { get; set; }
// Processing time
public DateTime? DealTime { get; set; }
// Dealer
public Guid DealerId { get; set; }
// Dealer number
[Required]
[MaxLength(EntityDefault.FieldLength_50)]
public string DealerCode { get; set; }
// Name of dealer
[Required]
[MaxLength(EntityDefault.FieldLength_100)]
public string DealerName { get; set; }
// Resume source
public VehicleWarrantyRepairHistorySource Source { get; set; }
// Cumulative maintenance days
public int? TotoalRepairDays { get; set; }
// After sale 60 Days /3000KM Number of serious failures in
public int? FNum { get; set; }
// Cumulative number of serious safety performance failures
public int? GNum { get; set; }
// Cumulative replacement times of engine assembly
public int? HNum { get; set; }
// Cumulative replacement times of gearbox assembly
public int? INum { get; set; }
// Maximum replacement times of main engine parts
public int? JNum { get; set; }
// Maximum replacement times of main parts of gearbox
public int? KNum { get; set; }
// Same as 1 Maximum replacement times of main parts
public int? LNum { get; set; }
// Same as 1 Maximum cumulative number of product quality problems (parts + Malfunction + Azimuth)
public int? MNum { get; set; }
// Same as 1 Maximum cumulative number of product quality problems
public int? NNum { get; set; }
}Here we post the first simple query example, through the Include method to query the associated three packages of warning level this entity, in our example, the result we return is paged, and will be filtered according to the Dto passed by the front end, here we look at how this code is entity.
/// <summary>
/// Enquiry vehicle 3 Package information
/// </summary>
/// <param name="input"> Query input </param>
/// <param name="pageRequest"> Paging request </param>
/// <returns> Paged 3 Package early warning vehicle information </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. Actual sales completion && 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);
}Here we can see that the associated entity can be queried through one Include. Why can it be implemented? It is because there are foreign keys of WarrantyWarningLevel entities in VehicleWarranty entities, and entities associated with foreign keys are added here, so that InClude methods can be used correctly. Moreover, this InClude method can only take entities as parameters, not foreign keys as parameters. Here, I want to ask a question: Is SQL (SqlServer database) finally generated here left join or inner join? Before you finish reading the following analysis, you need to think about it.
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]Here we see that the generated SQL statement is left join, so why not inner join? Let me show you the specific answers first. Do you understand here? The problem is that the foreign key I set up here is public Guid, which can be nullable? WarningLevelId {get; set;}, if it is a non-null foreign key, then the generated SQL is inner join. You can try it yourself. Another thing to remind is that, If you establish the relationship between VehicleWarranty and WarrantyWarningLeve as in the above entity, Migration to the database will generate foreign key constraints by default, which needs special attention when using, but if you only add foreign keys without adding corresponding foreign keys, entities with the same name will not generate foreign key constraints, so you don't understand the implementation mechanism for the time being.
2 The main manifest uses Include
What we just introduced is the 1-to-1 association relationship, so there is an obvious main list relationship between VehicleWarranty and VehicleWarrantyRepairHistory, that is, what kind of SQL statement will be generated when one VehicleWarranty corresponds to multiple VehicleWarrantyRepairHistory? I also post the code here, and then analyze the generated SQL statement.
/// <summary>
/// Query specific 3 Package early warning vehicle information
/// </summary>
/// <param name="id"> Specific Id</param>
/// <returns> Specific 3 Package early warning vehicle information </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(" Cannot find the current specific 3 Package early warning vehicle information ");
}
var retResult = ObjectMapper.Map<GetVehicleWarrantyWithDetailsOutput>(query);
return retResult;
}Two InClude methods are used here, so how does EFCore generate this SQL? By querying the final SQL, we found that EFCore is queried separately when dealing with such problems, and then merged into the queried entities, so the SQL generated in the process of this query is as follows:
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]This in the process of query will be divided into several SQL query and will be the results of the previous query as part of the conditions of the later query, after the entire query is completed, these results will be combined into a query object in memory.
3 Usage of ThenInclude
After the above introduction, You should be able to understand the specific meaning and usage of this Include. Following the above example, If there are other entities associated with Id through foreign key Id in WarrantyWarningLevel, At this time, ThenInclude came in handy, Theoretically, as long as this foreign key relationship is established between each other, it can go straight to ThenInClude, but under 1 general situation, it will not be used in such a complicated situation. Of course, every Include is also carried out as a separate query. This can also be tested with specific examples, and a specific example is posted here.
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[" Current maintenance contract does not exist "]);
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;
}Finally, I would like to introduce a very special case. Since the ThenInclude method can only be carried out layer by layer, if I want to do ThenInclude operation on two related entities in the same entity, I will give the code directly here.
/// <summary>
/// Events of completion of maintenance contract
/// </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);
}Here, Include is required to be the same as an entity twice, and then the ThenInclude method is called respectively, which is a special case and needs attention when using it.
Tips:
Here, readers may not understand the specific origin of _ repairContractRepository when looking at the code. Here, a complete code is posted.
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>
/// Events of completion of maintenance contract
/// </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 Usage of IncludeFilter
In some scenarios, we may need to bring out the list and filter it. This function is an improvement to Include method. You can combine the two operations into one, This need to note when using this is not Asp. Net Core features, this need to be introduced package Z. EntityFramework. Plus. EFCore. dll package to achieve, if your system is using ABP as the main framework of the project, then you only need to reference Abp. EntityFrameworkCore. EFPlus package is OK, because this package contains Z. EntityFramework related sub-package, this need to be noted when using.
Let's look at 1 to see how it is used in our code.
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. Effective );
if (company == null) {
throw new EntityNotFoundException(SharedLocalizer[" The corresponding enterprise could not be found "]);
}
return (company, currentPartSaleOrderTypes);
}This provides a new way to filter the list, which not only improves the efficiency but also makes the code more optimized and concise.
5 Special circumstances
It also introduces a way to get the list without Include method. Just like the following writing, Company object and OrderingCalendars between the establishment of a 1-to-many navigation attributes, we use company. OrderingCalendars before the internal inventory query out (do not need to define variables to receive, use _), this is also through the navigation attributes automatically map to OrderingCalendars of company to go, this need to pay special attention when using, query ToList in advance, the data query into memory
_ = _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
});The above is the detailed explanation of the navigation attributes in EFCore. For more information about the navigation attributes of EFCore, please pay attention to other related articles on this site!