CODEWITHSUNDEEP
linqsql-to-linq-conversion
Suyash Gupta
Suyash Gupta

Reputation: 576

Linq equivalent to a Sql Query

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.

Upvotes: 0

Views: 49

Answers (1)

Suyash Gupta
Suyash Gupta

Reputation: 576

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();

Upvotes: 1

Related Questions