c# - 如何强制 Entity Framework 生成更高效的 SQL 代码？

Question

我们正在使用 EF 6.1。尽管 v4 有所改进，但通常需要帮助 EF 决定如何更高效地生成 SQL。通常有助于在我们的案例中使用 LINQ 并指定连接。

但是，现在我有一个情况，我不知道该怎么做(除了完全绕过 EF 之外):

return db.Testlets.Include("TestTasks.TestQuestions.TestAnswers")
         .Include("TestTasks.TestQuestions.TestQuestionCriterionGroups.TestQuestionCriterions")
         .Include("TestTasks.TestQuestions.Question.Answers")
         .Where(x => x.TestId == testId && x.ShownOn.HasValue)
         .ToList();

这会产生非常低效的代码。事实上，如果 EF 产生这样的东西就足够了，也是最好的:

SELECT * 
FROM TestLet TL
INNER JOIN TestTask TT ON TL.Guid = TT.TestletId
INNER JOIN TestQuestion TQ ON TT.Guid = TQ.TestTaskId
INNER JOIN TestAnswer TA ON TQ.Guid = TA.TestQuestionId
LEFT OUTER JOIN TestQuestionCriterionGroup TQCG ON TQCG.TestQuestionId = TQ.Guid
LEFT OUTER JOIN TestQuestionCriterion TQC ON TQCG.Guid = TQC.TestQuestionCriterionGroupId
INNER JOIN Question Q ON TQ.QuestionId = Q.QuestionId AND Q.IsActive = 1
INNER JOIN Answer A ON Q.QuestionId = A.QuestionId AND A.IsActive = 1
WHERE 
    TL.TestId='59ADFB3F-16A6-46E0-8054-7F6E83414DC9'
    AND TL.ShownOn IS NOT NULL

我到了下面的代码(最后没有包含)生成上面的 SQL 的地步，但只选择了 teSTLet 列(没有应用包含，因为它们不存在，因此没有映射到 EF 实体) 并且我需要急切加载整个层次结构。当我最后添加 include 时，生成的 SQL 再次变得糟糕且非常慢:

                (from tl in
                db.Testlets.Where(tl => tl.TestId == testId && tl.ShownOn.HasValue)
                from tt in db.TestTasks.Where(tt => tl.Guid == tt.TestletId)
                from tq in db.TestQuestions.Where(tq => tt.Guid == tq.TestTaskId)
                from ta in db.TestAnswers.Where(ta => tq.Guid == ta.TestQuestionId)
                from q in db.Questions.Where(q => tq.QuestionId == q.Id)
                from a in db.Answers.Where(a => q.Id == a.QuestionId)
                from tqcg in
                    db.TestQuestionCriterionGroups.Where(tqcg => tq.Guid == tqcg.TestQuestionId).DefaultIfEmpty()
                from tqc in
                    db.TestQuestionCriterions.Where(tqc => tqcg.Guid == tqc.TestQuestionCriterionGroupId)
                        .DefaultIfEmpty()
                select tl).Include("TestTasks.TestQuestions.TestAnswers")
                .Include("TestTasks.TestQuestions.TestQuestionCriterionGroups.TestQuestionCriterions")
                .Include("TestTasks.TestQuestions.Question.Answers") 

有谁知道如何编写 linq2sql 或 entities2sql 代码，这将是有效的并且有正确的结果？或者对于这种更复杂的场景，是否只有放弃 EF 的方法？如果是这样，如何以最简单的方式映射回 EF 结构(从带有上述连接的 SQL)？

如果有人想知道更多关于如何进行左连接的信息:https://msdn.microsoft.com/en-us/library/bb397895.aspx

以及为什么在开头的查询中指定时 Includes 不起作用: http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx

更新:生成的 sql 的要点:https://gist.github.com/Ondrashx/d0347fc807f0f7fbdf46

Answer 1

将查询分成两部分。在 1 中加载 TeSTLets、TestTasks、TestQuestions、TestAnswers，然后在一秒钟内加载其余的——假设 ObjectContexts 具有像 DbContext 那样的自动修复:

类似于:

var results=db.Testlets.Include("TestTasks.TestQuestions.TestAnswers")
    .Where(x => x.TestId == testId && x.ShownOn.HasValue)
    .ToList();

然后加载 child :

var questionIds=results.TestQuestions.Select(tq=>tq.Guid).ToArray();

db.TestQuestions
    .Include("TestQuestionCriterionGroups.TestQuestionCriterions")
    .Include("TestTasks.TestQuestions.Question.Answers")
    .Where(tq=>questionIds.Contains(tq.Guid))
    .Load();

我从未使用过 ObjectContext，但 DbContext 会加载子对象，并在第一个查询中自动修复代理，以便它们都被填充。 (或者应该——我做类似的事情，但加载整个表，而不仅仅是选择的部分)。

如果您的性能问题是由结果集变得太大并且需要传输然后丢弃冗余列数据引起的，这应该有效。如果需要，您当然可以将查询进一步分解为 2 个查询以上，但您需要在传输/处理更少的冗余列与更多的数据库往返之间取得性能改进之间取得平衡。

你也可以尝试这样的事情(我自己从来没有做过，但看起来很有希望......不确定它是否会加载 child )

var conn=new SqlConnection("{Your sqlconnection string}");
conn.Open();
var cmd=new SqlCommand("{Your query}",conn);
var dr=cmd.ExecuteReader();
var result=db.Translate<Testlets>(dr);