CODEWITHSUNDEEP
c#linq
Dave
Dave

Reputation: 13

Linq join with count

I have two tables, group and group_user_xref.

I need to list the groups with a column which counts how many users are in that group. At the minute the count I have seems to be counting only the group as it always returns 1.

So far I have : 

(from g in db.group
 join g2 in db.group_user_xref
 on g.ID equals g2.group_uid
 where g2.user_uid == user_auth_id 
 group g by new { g.ID, g.group_name, g.group_code, g.owner_id, g2.user_uid}   into x
 select new GroupSummary { ID = x.Key.ID, GroupOwner = x.Key.owner_id, GroupCode = x.Key.group_code, GroupName = x.Key.group_name, GroupCount = x.Count() }).ToList();

How can I get the counts of the users in each group?

Upvotes: 0

Views: 303

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

You group by added to the also the user id - so it gives you a grouping for each user in each group - and for that the count is one

(from g in db.group
join g2 in db.group_user_xref
on g.ID equals g2.group_uid
where g2.user_uid == user_auth_id 
group g by new 
{ 
    g.ID, 
    g.group_name, 
    g.group_code, 
    g.owner_id
}   into x
select new GroupSummary 
{ 
    ID = x.Key.ID, 
    GroupOwner = x.Key.owner_id, 
    GroupCode = x.Key.group_code, 
    GroupName = x.Key.group_name, 
    GroupCount = x.Count() 
}).ToList()

Upvotes: 1

Related Questions