'Entity Framework Core : performance slow when loading entities with deep includes
I am facing query performance issue when loading entities with a lot of Includes and ThenIncludes.
Each entity has around 2 to 3 levels of ThenIncludes.
The following is a sample of my entity class:
public class EntityA {
public EntityB ItemOne { get; set; }
public EntityC ItemTwo { get; set; }
public EntityD ItemThree { get; set; }
public EntityE ItemFour { get; set; }
public EntityF ItemFive { get; set; }
public EntityG ItemSix { get; set; }
public List<EntityI> ItemSeven { get; set; }
public List<EntityI> ItemEight { get; set; }
}
public class EntityB {
public EntityJ AnotherProperty { get; set; }
...
}
public class EntityJ {
public EntityK SomeProperty { get; set; }
}
... // Remaining entity classes
My current query:
protected virtual IQueryable<RecommendedBuild> QueryWithDetails => _queryWithDetails ??= _db.RecommendedBuilds
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.CasingType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.Color)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.SidePanelWindow)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.FrontPanelUsbs)
.ThenInclude(c => c.LookValue)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Casing)
.ThenInclude(c => c.MotherboardFormFactors)
.ThenInclude(c => c.LookValue)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.CpuCooler)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.CpuCooler)
.ThenInclude(c => c.Bearing)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.CpuCooler)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.CpuCooler)
.ThenInclude(c => c.Sockets)
.ThenInclude(c => c.LookValue)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.Series)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.Family)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.Socket)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.IntegratedGraphic)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.Lithography)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Cpu)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.ChipSet)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.MemoryType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.Interface)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.Color)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.SliCrossFireType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.FrameSyncType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.Cooling)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.ExternalPower)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.GraphicCard)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.FormFactor)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.Chipset)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.MemoryType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.Color)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.Wifi)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.Socket)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Motherboard)
.ThenInclude(c => c.OnBoardEthernet)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.EffieciencyRating)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.ModularType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.Color)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.PsuType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.PowerSupply)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Variant)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.RamType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.RamVariants)
.ThenInclude(r => r.Color)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Rams)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.FormFactor)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Variant)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.Manufacturer)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.StorageType)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.FormFactor)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.Interface)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.ListOfStorageVariants)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Storages)
.ThenInclude(u => u.Part)
.ThenInclude(c => c.HardwareImage)
.Include(u => u.BuildDetail)
.ThenInclude(u => u.Owner)
.Include(u => u.RecommendedBuildCategory);
private IQueryable<RecommendedBuild> _queryWithDetails;
public RecommendedBuild GetBuildById(string id) {
return QueryWithDetails.Where(b => b.Id == id);
}
I have tried to debug the query generated by EF Core. It looks something like this:
SELECT // long list of columns
FROM EntityAs AS u
LEFT JOIN EntityBs As u1
... // other Included tables
LEFT JOIN (
SELECT A, B, C, Key
FROM SomeThenIncludedTables AS ux
INNER JOIN AgainThenIncludedTables AS uy ON ux.Key = uy.Key
) AS t ON uA.Key = t.Key
... // some other similar left joins
I would like to know what are the best practices for this kind of complex queries.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|