Linq-to-SQL: How to perform a count on a sub-select
- by Peter Bridger
I'm still trying to get my head round how to use LINQ-to-SQL correctly, rather than just writing my own sprocs.
In the code belong a userId is passed into the method, then LINQ uses this to get all rows from the GroupTable tables matching the userId. The primary key of the GroupUser table is GroupUserId, which is a foreign key in the Group table.
/// <summary>
/// Return summary details about the groups a user belongs to
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List<Group> GroupsForUser(int userId)
{
DataAccess.KINv2DataContext db = new DataAccess.KINv2DataContext();
List<Group> groups = new List<Group>();
groups = (from g in db.Groups
join gu in db.GroupUsers on g.GroupId equals gu.GroupId
where g.Active == true && gu.UserId == userId
select new Group
{
Name = g.Name,
CreatedOn = g.CreatedOn
}).ToList<Group>();
return groups;
}
}
This works fine, but I'd also like to return the total number of Users who are in a group and also the total number of Contacts that fall under ownership of the group.
Pseudo code ahoy!
/// <summary>
/// Return summary details about the groups a user belongs to
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List<Group> GroupsForUser(int userId)
{
DataAccess.KINv2DataContext db = new DataAccess.KINv2DataContext();
List<Group> groups = new List<Group>();
groups = (from g in db.Groups
join gu in db.GroupUsers on g.GroupId equals gu.GroupId
where g.Active == true && gu.UserId == userId
select new Group
{
Name = g.Name,
CreatedOn = g.CreatedOn,
// ### This is the SQL I would write to get the data I want ###
MemberCount = ( SELECT COUNT(*) FROM GroupUser AS GU WHERE GU.GroupId = g.GroupId ),
ContactCount = ( SELECT COUNT(*) FROM Contact AS C WHERE C.OwnerGroupId = g.GroupId )
// ### End of extra code ###
}).ToList<Group>();
return groups;
}
}
