LINQ A List inside a join query

Question

I'm facing a problem while using LINQ and i wanted to know if there is a fast solution for this code inside a repository

GetCompletePackQuestion(int id)
    {
        var query = from q in DbSet where q.id == id
                    join a in DbContext.Set<answers>() on id equals a.question_id
                    join cc in DbContext.Set<correct_answers>() on id equals cc.question_id
                    select new CompletePackModel
                    {
                        Id = q.id,
                        Question = q.question,
                        CorrectAnswer = cc.answers.id,
                        Answers = q.answers.Select(ans => ans.answer)
                    };

        return query.SingleOrDefault();
    }

The CompletePackModel has the Answers property as a list of string.. So the question is: there are many answers to one question so while q.id is an int, question a string and answer_id a single id (well really i should take the string in the table connected to that id) ... the last one a.answer should be a list...

Is there a way this can work out?

Thanks

---

Why does it give me 3 result in the query instead of one?

Answer 1

I'm assuming that you're using Entity Framework (based on the DbContext object that I see). In which case, your Question class should have a collection of answers already on it. You would then need to do something like:

select new CompletePackModel
{
  Id = q.id,
  Question = q.question,                            
  CorrectAnswer = from answ in DbContext.Set<answers>() where answ.id == a.id select answ.answer,
  Answers = q.answers.Select(a => a.answer).ToList()
};

Answer 2

Will this do the trick?

var query = from q in DbContext.Set<questions>()
                join a in DbContext.Set<answers>() on q.id equals a.question_id
                join cc in DbContext.Set<correct_answers>() on q.id equals cc.question_id
                select new CompletePackModel
                {
                    Id = q.id,
                    Question = q.question,                            
                    CorrectAnswer = cc.answer,
                    Answers = DbContext.Set<answers>().Where(answ => answ.question_id == q.id).ToList()
                };