Since version 3.0.0, each Include will cause an additional JOIN to be added to SQL queries produced by relational providers, whereas previous versions generated additional SQL queries. This can significantly change the performance of your queries, for better or worse. In particular, LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries in order to avoid the cartesian explosion problem.
What is the thought here? How should one write large LINQ queries with EF Core 3+? I have a LINQ query which works perfectly fine with EF Core 2.2.6 but which times out on EF Core 3.1.
var data = await _repository.All<TransportModeEntity>() .Include(im => im.TransportMode_InsuranceType).ThenInclude(it => it.InsuranceType) .Include(im => im.TransportMode_PackingMode).ThenInclude(pm => pm.PackingMode) .Include(im => im.TransportMode_StateOfGoods).ThenInclude(sg => sg.StateOfGoods) .Include(im => im.TransportMode_LoadingMode).ThenInclude(lm => lm.LoadingMode) .Include(im => im.TransportMode_BulkTypes).ThenInclude(bt => bt.BulkType) .Include(im => im.TransportMode_VesselTypes).ThenInclude(vt => vt.VesselType) .Include(im => im.TransportMode_VesselAges).ThenInclude(vt => vt.VesselAge) .Include(im => im.TransportMode_VesselOwnerships).ThenInclude(vo => vo.VesselOwnership) .Include(im => im.TransportMode_FreightServices).ThenInclude(fs => fs.FreightService) .Include(im => im.TransportMode_SurCharges).ThenInclude(sc => sc.SurCharge) .Include(im => im.TransportMode_TemperatureControlled).ThenInclude(tc => tc.TemperatureControlled) .Include(im => im.TransportMode_ExtraClauses).ThenInclude(ec => ec.ExtraClause) .Include(im => im.TransportMode_DangerousGoods).ThenInclude(dg => dg.DangerousGoods) .Include(im => im.TransportMode_LiftRates).ThenInclude(lr => lr.LiftRate) .Include(im => im.TransportMode_WmRates).ThenInclude(wr => wr.WmRate) .Include(im => im.TransportMode_WeightConversions).ThenInclude(wc => wc.WeightConversion) .Include(im => im.TransportMode_CountryExcluded).ThenInclude(ce => ce.ProfileCountry) .Include(im => im.TransportMode_StateExcluded).ThenInclude(se => se.ProfileState) .ToListAsync();
Every "TransportMode_XXXX" is a virtual ICollection of a certain type. Granted the query is quite large, but as explained earlier it works perfectly fine in EF Core 2.2.6. What does Microsoft mean by "LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries"? How should one go about doing this in the best way possible from a performance perspective and would the refactoring lead to equal, better or worse performance than how it works as is in EF Core 2.2.6?
For now, until I've found a good way forward, I have only upgraded the API to ASP.NET Core 3.1 but stayed with EF Core 2.2.6.