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;
}
}
