Upgrading from Entity Framework Core 2.2 to 3+ (problems with many includes)

asp.net-core entity-framework entity-framework-core

Question

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.

https://docs.microsoft.com/en-us/ef/core/querying/related-data

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.

1
1
1/9/2020 6:13:12 PM

Popular Answer

Looks like you encounter this issue: https://github.com/dotnet/efcore/issues/19571

0
2/4/2020 11:17:31 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow