Get foreign key table with a single query using LINQ

c# entity-framework linq

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?

1
2
7/24/2012 3:39:57 PM

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(),
            };
6
7/24/2012 3:47:56 PM

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow