Get foreign key table with a single query using LINQ

c#, entity-framework, linq

Solution

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

Problem

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?

Original source