Hi I am trying to convert TSQL to Linq in my web api but I am not getting the correct results.
My existing TSQL is :
SELECT COUNT(a.Id) as total ,c.Name as classroom
From Attendance a INNER JOIN Classroom c ON a.classroom = c.Id
WHERE DropDate= '20160815'
GROUP BY c.Name
var name = (from a in dbContext.attendances
join c in dbContext.classrooms on a.Classroom equals c.Id
let classroom = c.Name
where (a.DropDate == today)
group a.Classroom by c.Name into g
classroom = g.Key,
total = g.Count()
You are returning the count of all attendances in the table by doing a seperate "sub-query" at
dbContext.attendances.Count(). What you want to do is
Total = g.Count() which gives you the count per each group.