snowflakes74 snowflakes74 - 3 months ago 9
C# Question

How to get Linq return Count as column on a grouped query

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


My attempt to convert to LINQ has been below:

THIS IS THE UPDATED LINQ (ANSWERED FROM Siraj Mansour)

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
select new
{
classroom = g.Key,
total = g.Count()
});


But the problem is it does not bring back the same data and returns a Total of 500 for each classroom. If someone can please let me know what am I doing incorrect here.

Answer

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.