如何强制实体框架生成更高效的SQL代码

本文关键字：高效 SQL 代码 何强制 实体 框架 | 更新日期: 2023-09-27 17:49:27

我们使用的是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

以及为什么include在查询开头指定时不起作用: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

如何强制实体框架生成更高效的SQL代码

将查询拆分为两个。在1中加载Testlets, TestTasks, TestQuestions, TestAnswers，然后在第二秒中加载其余的——假设ObjectContexts具有像DbContext一样的自动修复功能:

类似:

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

然后加载子元素:

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将加载子对象，并在第一个查询中自动修复代理，以便它们都被填充。(或者应该——我做了类似的事情，但加载整个表，而不仅仅是一个select部分)。

如果您的性能问题是由于结果集变得太大并且需要传输然后丢弃冗余列数据而引起的，那么

应该可以工作。当然，如果需要的话，您可以将查询分解为2个以上的查询，但是您需要通过传输/处理更少的冗余列和更多的数据库往返来平衡性能改进。

你也可以试试这样的东西(我自己从来没有做过，但看起来很有前途…不确定它是否会加载子节点)

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);
相关文章：