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>特定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]