c# - Linq 查询的复杂性限制

Question

我是 Linq 的忠实粉丝，我一直很享受表达式树等的强大功能。但我发现，每当我试图对查询变得过于聪明时，我就会遇到框架中的某种限制:虽然查询可能需要很短的时间才能在数据库上运行(如性能分析器所示)，但结果需要 ages 才能实现。当发生这种情况时，我知道我太花哨了，我开始将查询分解成更小的、一口大小的 block - 所以我有一个解决方案，尽管它可能并不总是最佳的。

但我想明白:

• 是什么插入了 Linq 框架在实现查询结果方面的优势？
• 我在哪里可以了解具体化查询结果的机制？
• 是否存在应避免的 Linq 查询的特定可衡量复杂性限制？
• 已知哪些设计模式会导致此问题，哪些模式可以解决此问题？

编辑:按照评论中的要求，这里有一个查询示例，我测得它可以在几秒钟内在 SQL Server 上运行，但花了将近 2 分钟才实现。我不会尝试在上下文中解释所有内容；它在这里只是为了让您可以查看构造并查看我正在谈论的示例:

Expression<Func<Staff, TeacherInfo>> teacherInfo =
    st => new TeacherInfo
    {
        ID = st.ID,
        Name = st.FirstName + " " + st.LastName,
        Email = st.Email,
        Phone = st.TelMobile,
    };

var step1 =
    currentReportCards.AsExpandable()
        .GroupJoin(db.ScholarReportCards,
                             current =>
                             new { current.ScholarID, current.AcademicTerm.AcademicYearID },
                             past => new { past.ScholarID, past.AcademicTerm.AcademicYearID },
                             (current, past) => new
                             {
                                 Current = current,
                                 PastCards =
                                 past.Where(
                                     rc =>
                                     rc.AcademicTerm.StartDate <
                                     current.AcademicTerm.StartDate &&
                                     rc.AcademicTerm.Grade == current.AcademicTerm.Grade &&
                                     rc.AcademicTerm.SchoolID == current.AcademicTerm.SchoolID)
                             });
// This materialization is what takes a long time:
var subjects = step1.SelectMany(x => from key in x.Current.Subjects
                            .Select(s => new { s.Subject.SubjectID, s.Subject.SubjectCategoryID })
                            .Union(x.PastCards.SelectMany(c => c.Subjects)
                                            .Select(
                                                s => new { s.Subject.SubjectID, s.Subject.SubjectCategoryID }))
         join cur in x.Current.Subjects on key equals
             new { cur.Subject.SubjectID, cur.Subject.SubjectCategoryID } into jcur
         from cur in jcur.DefaultIfEmpty()
         join past in x.PastCards.SelectMany(p => p.Subjects) on key equals
             new { past.Subject.SubjectID, past.Subject.SubjectCategoryID } into past
         select new
         {
             x.Current.ScholarID,
             IncludeInContactSection =
                 // ReSharper disable ConstantNullCoalescingCondition
                (bool?)cur.Subject.IncludeInContactSection ?? false,
             IncludeGrades = (bool?)cur.Subject.IncludeGrades ?? true,
             // ReSharper restore ConstantNullCoalescingCondition
             SubjectName =
                cur.Subject.Subject.Name ?? past.FirstOrDefault().Subject.Subject.Name,
             SubjectCategoryName = cur.Subject.SubjectCategory.Description,
             ClassInfo = (from ce in myDb.ClassEnrollments
                             .Where(
                                 ce =>
                                 ce.Class.SubjectID == cur.Subject.SubjectID
                                 && ce.ScholarID == x.Current.ScholarID)
                             .Where(enrollmentExpr)
                             .OrderByDescending(ce => ce.TerminationDate ?? DateTime.Today)
                     let teacher = ce.Class.Teacher
                     let secTeachers = ce.Class.SecondaryTeachers
                     select new
                     {
                         ce.Class.Nickname,
                         Primary = teacherInfo.Invoke(teacher),
                         Secondaries = secTeachers.AsQueryable().AsExpandable()
                            .Select(ti => teacherInfo.Invoke(ti))
                     })
                .FirstOrDefault(),
             Comments = cur.Comments
                 .Select(cc => new
                 {
                     Staff = cc.Staff.FirstName + " "
                                     + cc.Staff.LastName,
                     Comment = cc.CommentTemplate.Text ??
                                         cc.CommentFreeText
                 }),
             // ReSharper disable ConstantNullCoalescingCondition
             DisplayOrder = (byte?)cur.Subject.DisplayOrder ?? (byte)99,
             // ReSharper restore ConstantNullCoalescingCondition
             cur.Percentile,
             cur.Score,
             cur.Symbol,
             cur.MasteryLevel,
             PastScores = past.Select(p => new
                {
                    p.Score,
                    p.Symbol,
                    p.MasteryLevel,
                    p.ScholarReportCard
                     .AcademicTermID
                }),
             Assessments = cur.Assessments
                 .Select(a => new
                 {
                     a.ScholarAssessment.AssessmentID,
                     a.ScholarAssessment.Assessment.Description,
                     a.ScholarAssessment.Assessment.Type.Nickname,
                     a.ScholarAssessment.AssessmentDate,
                     a.ScoreDesc,
                     a.ScorePerc,
                     a.MasteryLevel,
                     a.ScholarAssessment.Assessment.Type.AssessmentFormat,
                     a.ScholarAssessment.PublishedStatus,
                     a.ScholarAssessment.FPScore,
                     a.ScholarAssessment.TotalScore,
                     a.ScholarAssessment.Assessment.Type.ScoreType,
                     a.ScholarAssessment.Assessment.Type.OverrideBelowLabel,
                     a.ScholarAssessment.Assessment.Type.OverrideApproachingLabel,
                     a.ScholarAssessment.Assessment.Type.OverrideMeetingLabel,
                     a.ScholarAssessment.Assessment.Type.OverrideExceedingLabel,
                 })
            })
        .ToList();

Answer 1

Linq 对某些任务使用延迟执行，例如在遍历 IEnumerable<> 时，所以你所谓的物化包括一些实际的数据获取。

var reportCards = db.ScholarReportCards.Where(cr => ...); // this prepares the query 
foreach (var rc in reportCards) {} // this executes your query and calls the DB

我认为，如果您在 SQL 服务器上跟踪/计时查询，您可能会看到一些查询在“具体化”步骤中到达。 the "Select N+1" problem 等反模式甚至可能会加剧此问题。 :例如，您似乎没有包括 AcademicTerm您请求中的对象；如果你不解决这些将导致选择 N+1，即每个 ScholarReportCard将调用数据库以延迟解析 AcademicTerm附上。

如果我们专注于 Linq to DB 方面，至少尽量不要:

• 选择 n+1:Include您需要的相关数据表
• 选择的数据过多:仅在选择中包含您需要的列(您需要的表格上的 Include)