Can I make this Linq to EF more efficient?

Question

I have a design where we store answers to scripts's questions by given users. One script can have many questions and each question can be answered multiple times by a given user.

The MS SQL tables look (removing extra details) more or less like:

-Scripts
ScriptId int (PK, identity)

-ScriptQuestions
ScriptId int    (PK)
QuestionId int  (PK)

-Questions
QuestionId int (PK, identity)
QuestionText varchar

-Answers
AnswerId int (PK, identity)
QuestionId int
UserId  int
AnswerText varchar

I would like to query this database for a given script and a given user and obtain all questions and the last answer provided for each question (if any). In T-SQL I would do something like this:

SELECT 
    sq.QuestionId,
    q.QuestionText,
    la.AnswerText   
FROM    
    ScriptQuestions sq
        ON s.ScriptId = sq.ScriptId
    INNER JOIN Questions q
        ON sq.QuestionId = q.QuestionId
    LEFT OUTER JOIN (
            SELECT
                QuestionId,
                AnswerText
            FROM Answers
            WHERE AnswerId IN (
                        SELECT 
                            MAX(AnswerId) LastAnswerId
                        FROM Answers
                        WHERE UserId = @userId
                        GROUP BY QuestionId
                    )
            ) la
        ON q.QuestionId = la.QuestionId
WHERE
    sq.ScriptId = @scriptId

(untested, but I think it's close to what I would do)

I am using LinqToEF on an MVC 3 application, and to get those results I used:

        var questions = from sq in script.ScriptQuestions
                        select new QuestionsAnswers
                                   {
                                       QuestionId = sq.QuestionId,
                                       QuestionText = sq.Question.QuestionText,
                                       LastAnswer = sq.Question.Answers
                                           .Where(a => a.UserId == userId)
                                           .OrderByDescending(a => a.AnswerId)
                                           .Select(a => a.AnswerText)
                                           .FirstOrDefault()
                                   };

And I do get the same results BUT when I run the Intellitrace profiler from VS 2010 I can see that linq translates this into sending a SELECT statement for EVERY QUESTION on the script and then ANOTHER SELECT statement for every answer. So if the scripts has 20 questions, it would be querying the database at least 40 times instead of sending just one SQL statement as above.

I tried to change the way I create my LinqToEF statement but I was not able to overcome the n SELECT statements issue.

This cannot be the right way, or is it?

Answer 1

My guess is that your query uses LINQ to Objects in memory in combination with lazy loading of the navigation properties because your query starts with script.ScriptQuestions which is apparently not an IQueryable. So the collection is iterated in memory and for every entry you access the sq.Question and sq.Question.Answers navigation properties. If lazy loading is enabled each time you access these properties a new query is issued to the DB to populate the properties. Your filter on the sq.Question.Answers collection is performed in memory on the full collection.

You can try to change it the following way:

    var questions = from sq in context.ScriptQuestions
                    where sq.ScriptId == script.ScriptId
                    select new QuestionsAnswers
                               {
                                   QuestionId = sq.QuestionId,
                                   QuestionText = sq.Question.QuestionText,
                                   LastAnswer = sq.Question.Answers
                                       .Where(a => a.UserId == userId)
                                       .OrderByDescending(a => a.AnswerId)
                                       .Select(a => a.AnswerText)
                                       .FirstOrDefault()
                               };

This should be only one single database query because now it's LINQ to Entities with IQueryable.