CODEWITHSUNDEEP
c#linqt-sql
snowflakes74
snowflakes74

Reputation: 1307

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.

Upvotes: 0

Views: 76

Answers (1)

sm_
sm_

Reputation: 2602

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.

Upvotes: 1

Related Questions