EF Core 3.0.Include无法按预期工作，而且速度非常慢

Question

我在EF Core 2.0中有这样的linq查询，它可以正常工作，但当我升级到EF Core 3.0时，它总是超时。我在query = query.Where(x => x.Questions);中发现了问题
我的问题是，我想返回带有筛选问题的课程，如仅Take(10(或with。Where条件仅显示特定范围，而不是所有问题。

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更改了使用.Include()生成的查询，并且您遇到了笛卡尔爆炸问题；

具体来说，现在文档中有以下红色警告：

注意

自3.0.0版本以来，每个Include都将导致一个额外的JOIN添加到关系提供程序生成的SQL查询中，而以前的版本生成了额外的SQL查询。这个可以显著更改查询的性能，以获得更好的或更糟的特别是，LINQ查询的Include运算符可能需要分解为多个单独的运算符LINQ查询是为了避免笛卡尔爆炸问题。

解决方案是现在对每个文档执行多个查询。

它的超级不幸的加载实体图，对于高度规范化的数据来说是常见的，是非常不性能的，但这是它与EF的当前状态。

请参阅：加载相关数据并滚动，直到您看到红色。

Answer 2

var query = _courseRepository.Table
.Include(x => x.Questions)
.Include(x => x.CourseClasses)
.Include(x => x.CourseYear)
.Include(x => x.CourseSubject);
var course = await query.FirstOrDefaultAsync(x => x.Id == id);
query.Include(x => x.Instructors).ThenInclude(y => y.User).SelectMany(a => a.Instructors).Load();
query.Include(x => x.Instructors).ThenInclude(y => y.Course).SelectMany(a => a.Instructors).Load();
query.Include(x => x.Instructors).ThenInclude(y => y.CourseClass).SelectMany(a => a.Instructors).Load();
query.Include(x => x.CourseSections).ThenInclude(y => y.Lessons).SelectMany(a => a.CourseSections).Load();
query.Include(x => x.UserCourses).ThenInclude(y => y.User).SelectMany(a => a.UserCourses).Load();