EF Core 3.0 .Include does not work as expected and Super Slow

Question

I have linq query like this in EF Core 2.0, It work as it is, but when I upgrade to EF Core 3.0 it always timeout. I found the issue in query = query.Where(x => x.Questions);.
My Question is i would like to return the course with filter questions like only Take(10) or with .Where condition that only display certain range not all questions.

var query = _courseRepository.Table;
query = query.Where(x => x.Id == id);
query = query.Include(x => x.Questions);
query = query.Include(x => x.CourseYear);
query = query.Include(x => x.CourseSubject);
query = query.Include(x => x.Instructors).ThenInclude(y => y.User);
query = query.Include(x => x.Instructors).ThenInclude(y => y.Course);
query = query.Include(x => x.Instructors).ThenInclude(y => y.CourseClass);
query = query.Include(x => x.CourseSections);
query = query.Include(x => x.CourseSections).ThenInclude(y => y.Lessons);
query = query.Include(x => x.CourseClasses);
query = query.Include(x => x.UserCourses).ThenInclude(y => y.User);
var result = query.FirstOrDefault();

Answer 1

EFCore 3.0 changed the query(ies) generated by using .Include() and you are experiencing the Cartesian Explosion Problem;

Specifically there is the following Red Caution in the Docs now:

Caution

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.

The solution is to execute multiple queries now per the docs.

Its super unfortunate loading entity graphs, common to highly normalized data, is so un-performant but this is its current state with EF.

See: Loading Related Data and scroll until you see red.