LINQ to SQL Grouping and Counting

Question

I have searched for solution, but nothing similar. My problem is that I want to select data from database, group it by UserID and Count it by Status id

Users

UserID
Name

Appointments

UserID
ClientID
Status
StartDate

Status can be active=1, canceled=2, done=3

This is how I will display results.

Results table

Thanks in advance.

You're actually pivoting the data here; not something linq is great at but this might help http://stackoverflow.com/questions/963491/pivot-data-using-linq — Dave, Jan 30 '12 at 17:15

Magnus · Accepted Answer · 2012-01-30T17:32:59.567

In you question you say you want to group on UserId, but in the output you show Name. This query will group on both. You might want to adjust it to your needs.

from u in tblUsers
join a in tblAppointments on u.UserID equals a.UserID
group a by new { u.UserID, u.Name } into g
select new
{
    Name     = g.Key.Name,
    Active   = g.Count (x => x.Status == 1),
    Canceled = g.Count (x => x.Status == 2),
    Done     = g.Count (x => x.Status == 3)
}

(this will handle the case if two users have the same name though)

score 0 · Answer 2 · answered Jan 30 '12 at 17:16

0

This covers grouping operators. This covers the count.

answered Jan 30 '12 at 17:16

Jimmy Miller

1,317
9
13

LINQ to SQL Grouping and Counting

2 Answers2