Entity Framework lazy loading issue

Question

I am trying to create a multiple choice questionnaire that is table-driven. There is a Question that has child Choices within each question.

When I iterate the listOfQuestions, the first SQL is executed. I thought by "including" Choices that this would prevent a secondary lookup from occuring when I loop though the Choices for the current Question, but it did not.

Why?

  var listOfQuestions = (from q in journeyEastContext.Questions.Include("Choices")
                                   orderby q.QuestionId
                                   select new
                                   {
                                       Question = q,
                                       Choices = q.Choices.OrderBy(c => c.Sequence)
                                   });


            foreach (var questionGroup in listOfQuestions)
            {

                Question question = questionGroup.Question;

                Literal paragraph = new Literal
                {
                    Text = "<P/>"
                };
                this.QuestionPanel.Controls.Add(paragraph);

                Label QuestionLabel = new Label
                {
                    Text = question.Text
                };
                this.QuestionPanel.Controls.Add(QuestionLabel);

                //var sortedChoices = from choices in question.Choices
                //                    orderby choices.Sequence
                //                    select choices;

                foreach (Choice choice in question.Choices)
                {
                    Literal carrageReturn = new Literal
                    {
                        Text = "<BR/>"
                    };
                    this.QuestionPanel.Controls.Add(carrageReturn);

                    RadioButton choiceRadioButton = new RadioButton()
                    {
                        ID = String.Format("{0},{1}", question.QuestionId, choice.ChoiceId),
                        Text = choice.Text,
                        GroupName = question.QuestionId.ToString()
                    };

                    this.QuestionPanel.Controls.Add(choiceRadioButton);
                }
            }

Answer 1

It is because of the projection being part of the query.

                                   select new
                               {
                                   Question = q,
                                   Choices = q.Choices.OrderBy(c => c.Sequence)
                               });

There are a few ways to approach the solution to this, the simplest would be

  var quesitonsList = (from q in journeyEastContext.Questions.Include("Choices")
                               orderby q.QuestionId).ToList();



var listOfQuestions = from q in questionsList
            Select new
                                   {
                                       Question = q,
                                       Choices = q.Choices.OrderBy(c => c.Sequence)
                                   });

This would tell EF to execute the first query (with the Choices property eagerly loaded) and then let you run through your iteration without having extra queries fired off.

.Include and .Select do not mix because of the type of query being generated in T-SQL. Basically projections use inner select statements and eagerly loaded properties use denormalization and joins to flatten the record set.