How to force Entity Framework to produce more efficient SQL code?

Question

We are using EF 6.1. Despite the improvements from v4, there is often need to help EF in the decision on how to generate SQL to be more efficient. Usually helps to use LINQ in our case and specify joins.

However, now I have a case where I don't know how to do it (besides circumventing the EF completely):

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();

This produces code which is very inefficient. In fact it should be enough and best if EF produced something like this:

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

I came to the point where the following code below (without includes in the end) produced the SQL above, but was selecting just columns of testlets (no Includes were applied, because they were not there and therefore no mapping to EF entities) and I need the whole hierarchy eagerly loaded. When I added the includes in the end, the generated SQL was again horrible and very slow:

                (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")

Does anybody know how to write linq2sql o entities2sql code which would be efficient and has the correct outcome? Or is there only the way of abandoning the EF for this more complex scenarios? If so how to do the mapping back to EF structures (from SQL with joins above) in the most easy way?

In case somebody wants to know more about how to do left joins: https://msdn.microsoft.com/en-us/library/bb397895.aspx

and why Includes do not work on when specified in the query at the beginning: http://blogs.msdn.com/b/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx

UPDATE: the gist with the generated sql: https://gist.github.com/Ondrashx/d0347fc807f0f7fbdf46

You really should include generated SQL, it can be easily retrieved by hooking up to DbContext.Database.Log. — Patryk Ćwiek
– Patryk Ćwiek, Commented May 15, 2015 at 13:42
have you considered returning an anonymous object containing the relevant fields from each related table. — B2K
– B2K, Commented May 15, 2015 at 15:11
@B2K Thanks for your sugestion. I would like to use the EF entities as they are now, because other code depend on them and I would like to have this chnage as little impact as possible. — Ondrej Peterka
– Ondrej Peterka, Commented May 15, 2015 at 15:37
@Patryk it is quite long so it is probably not good idea to put it here, I will post it somewhere and add the link here — Ondrej Peterka
– Ondrej Peterka, Commented May 15, 2015 at 15:37
The reason I suggested an anonymous object is because sql queries are not designed to return a hierarchical result. This leads to inherent inefficiencies when EF needs to recreate it from the tabular query results. — B2K
– B2K, Commented May 15, 2015 at 21:33

Robert McKee · Accepted Answer · 2015-05-18 15:24:51Z

1

Break the query into two. Load your Testlets, TestTasks, TestQuestions, TestAnswers in 1, and the remaining in a second -- assuming ObjectContexts have autofixups like DbContext does:

Something like:

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

Then load the children:

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();

I've never used the ObjectContext, but DbContext will load the children, and do automatic fix ups of the Proxies in the first query so that they are all filled. (or should -- I do something similar, but load the entire table, not just a select portion).

This should work if your performance problem is caused by the resultset becoming too large and needing to transmit and then throw away the redundany column data. You can of course, break the query up further than 2 queries if you need, but you'll need to balance the performance improvement from transmitting/processing fewer redundant columns with more round trips to the database.

You could try something like this as well (I've never done it myself, but it looks promising... Not sure if it will load the children either)

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);

edited May 18, 2015 at 15:24

answered May 15, 2015 at 18:01

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ondrej Peterka Over a year ago

Good point. However, this will not load the relations (which is why I am struggling with this in first place). Also I should note, that I have ObjectContext, so I would need to call ExecuteStoreQuery, but it should be equivalent (at least to my knowledge).

Collectives™ on Stack Overflow

How to force Entity Framework to produce more efficient SQL code?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related