Question
I have the following two tables
Groups
- Id
- Name
Members
- Id
- GroupId
- Name
I want to display a list of Groups with their member count. I can get all the groups and then use a foreach statement to count the number of members. But this is very inefficient.
Is there a way to write a LINQ query that will return the member count in a single query?
Accepted Answer
You can do it with a simple linq-to-entities query. You can either project it to an anonymous type or you can create your own class to store the information.
var query = from g in context.Groups
join m in context.Members on g.Id equals m.GroupId into members
select new
{
Group = g,
MemberCount = members.Count(),
};
Popular Answer
If there are foreign key relationships, wouldn't this work?
var q = context.Groups.Select(x => new { Group = x.Name, Count = x.Members.Count() } );
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
