public IQueryable<Pair<Question, IEnumerable<Pair<Answer, int>>>> LoadAnswerResponseCounts(int surveyId)
{
return from answer in this.Context.Answers
join question in this.Context.Questions on answer.QuestionId equals question.QuestionId
join section in this.Context.Sections on question.SectionId equals section.SectionId
where section.SurveyId == surveyId
orderby answer.RelativeOrder
group answer by question into answersByQuestion
orderby answersByQuestion.Key.RelativeOrder
select new Pair<Question, IEnumerable<Pair<Answer, int>>>
{
First = answersByQuestion.Key,
Second = from answer in answersByQuestion
join response in this.Context.Responses.Where(r => r.UserResponse != null
&& (r.ResponseHeader.Responses.Count(response => response.QuestionId == r.QuestionId && response.UserResponse != null) == 1
|| r.UserResponse == bool.TrueString)) // checkbox questions store true/false instead of null/answer-text
on answer.AnswerId equals response.AnswerId into answerResponses
from answerResponse in answerResponses.DefaultIfEmpty()
group answerResponse by answer into responsesByAnswer
select new Pair<Answer, int>
{
First = responsesByAnswer.Key,
Second = responsesByAnswer.Count(r => r != null)
}
};
}