How to use (left/right) outer join for this LINQ that join 3 entities?

Question

Using MVC 3, EF4.1:
Building a quiz screen, I am joining three entities: Steps, Questions, Responses

Each Step can have many questions, each question can have one or no responses

My issue is when I have no answers in my query, it returns steps with no questions. How do I incorporate an outer join (left/right) into this LINQ?

var steps = from s in db.Steps
                    join r in db.Responses.Where(x => x.ReviewID == id)
                           on s.StepID equals r.Question.StepID into g
                    orderby s.StepOrder
                    select new Quiz
                    {
                        StepID = s.StepID,
                        Title = s.Title,
                        Results = from x in g
                                  orderby x.Question.DisplayOrder
                                  select new Result
                                  {
                                      QuestionID = x.Question.QuestionID,
                                      DisplayOrder = x.Question.DisplayOrder,
                                      Choices = x.Question.Choices,
                                      ControlType = x.Question.ControlType,
                                      QuestionText = x.Question.QuestionText,
                                      AnswerValue = x.AnswerValue
                                  }
                    };

Question Model:

public class Question
    {
        public Question()
        {
            this.Responses = new List<Response>();
        }

        public int QuestionID { get; set; }
        public string QuestionText { get; set; }
        public Nullable<bool> Required { get; set; }
        public int DisplayOrder { get; set; }
        public int StepID { get; set; }
        public Nullable<int> DataType { get; set; }
        public Nullable<int> ControlType { get; set; }
        public string Choices { get; set; }
        public Nullable<int> MaxLength { get; set; }
        public virtual ICollection<Response> Responses { get; set; }
        public virtual Step Step { get; set; }
        public string NumberedQuestion
        {
            get { return String.Format("{0}. {1}", DisplayOrder, QuestionText); }
        }
    }

Response:

public class Response
    {
        public int ResponseID { get; set; }
        public int UserID { get; set; }
        public int QuestionID { get; set; }
        public string AnswerValue { get; set; }
        public int ReviewID { get; set; }
        public virtual Question Question { get; set; }
    }

Steps:

public Step()
        {
            this.Questions = new List<Question>();
        }

        public int StepID { get; set; }
        public int ReviewID { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public int StepOrder { get; set; }
        public virtual ICollection<Question> Questions { get; set; }

You've said you have three entites... please share the `Question` entity with us... — Jan P., Dec 20 '12 at 15:17

score 0 · Answer 1 · answered Dec 20 '12 at 15:46

Not very pretty... but does what it should ;)

var result =
    from step in db.Steps

    let questions = 
        db.Questions.Where(item => item.StepID == step.StepID)
    let responses = 
        db.Responses.Where(item => 
                item.ReviewID == id 
                && questions.Any(q => q.QuestionID == item.QuestionID))

    select new Quiz
    {
        StepID = step.StepID,
        Title = step.Title,
        Results =
            from question in questions.OrderBy(item => item.DisplayOrder)
            select new Result
            {
                QuestionID = question.QuestionID,
                DisplayOrder = question.DisplayOrder,
                Choices = question.Choices,
                ControlType = question.ControlType,
                QuestionText = question.QuestionText,
                AnswerValue = 
                    responses.Any(item => item.QuestionID == question.QuestionID)
                    ? responses.First(item => 
                        item.QuestionID == question.QuestionID).AnswerValue
                    : null
            }
    };

Risky Martin · Accepted Answer · 2012-12-20T17:24:15.083

The issue is that you're getting the question from the responses, so you're only getting questions that have responses. If you get the questions for each step and then the response for each question, it should work.

var steps = from s in db.Steps
            orderby s.StepOrder
            select new Quiz
            {
                StepID = s.StepID,
                Title = s.Title,
                Results = from question in s.Questions
                          orderby question.DisplayOrder                              
                          select new Result
                          {
                              QuestionID = question.QuestionID,
                              DisplayOrder = question.DisplayOrder,
                              Choices = question.Choices,
                              ControlType = question.ControlType,
                              QuestionText = question.QuestionText,
                              AnswerValue = question.Responses
                                                    .Where(r => r.ReviewID == id)
                                                    .Select(r => r.AnswerValue)
                                                    .FirstOrDefault()
                          }
            };

How to use (left/right) outer join for this LINQ that join 3 entities?

2 Answers2