Linq equivalent to a Sql Query

Question

SQL Query -

select q.QuesId, q.Title, q.Description, count(a.QuesId) as Answers
from Question q
join Answer a on q.QuesId = a.QuesId
group by q.QuesId, q.Title, q.Description

I want to convert this Sql query to linq.

My approach is -

var questions = (from q in db.Questions
                 join a in db.Answers on q.QuesId equals a.QuesId
                 group q by new 
                 { q.QuesId, q.Title, q.Description, q.AskedBy, q.AskedOn, q.ModifiedOn }
                 into x
                 select new 
                 { x.Key.QuesId, x.Key.Title, x.Key.Description, x.Key.AskedBy, x.Key.AskedOn, x.Key.ModifiedOn, x.key.Answers.count }
                 ).ToList();

It doesn't seem to be working.

Answer 1

This is what I end up doing which works well for me -

var questions = db.Questions
                .GroupJoin(db.Answers, q => q.QuesId, a => a.QuesId, (q, a) => q)
                .GroupBy(q => new { q.QuesId })
                .SelectMany(x => x.Select(q => new 
                { q.QuesId, q.Title, q.Description, q.UserDetail.FirstName, q.UserDetail.LastName, q.AskedOn, q.ModifiedOn, q.Answers.Count }
                )).Distinct();