Entity Framework Associated Table Counts with One Query

With the Entity Framework, we are able to use navigation properties to get the associated records. This however loads all the child rows for a parent which causes performance problems. In some instances, we may just need to get a count of the associated records without having to load all the rows.  Let’s see more detail how this can be done.

In the diagram below, we show the relationship between company, department and employee entities. We want to be able to get the company record with the aggregated count for departments and employees for each company. For this case, we can use the company navigation properties to get that counts and use a company view-model class to hold the additional information.

We start by first looking at our database entities:

We can see, the Company entity has the associations to the Department and Employee entities. We want to be able to return the Company information with associated counts using this model:

To load the company information with count records, we can use the following snippet:

    using (var context = new Entities())

           {

               companies = context.Companies.Select(c => new model.Company()

               {

                   Name = c.Name,

                   Id = c.Id,

                   EmployeeCount = c.Employees.Count(),

                   DepartmentCount = c.Departments.Count()

               }).ToList();

           }

With this lambda expression, we are using the navigation properties to get the count of the associated records. The query that is created and sent to the database is just one SQL statement that gets the company data and the associated rows count of the other tables.  The result is returned as a list of CompanyModel objects.

With this approach, we no longer need to load all the rows from the other tables using the navigation properties. This should  improve the overall performance of the application.

I hope you find this tip useful.

Originally posted at ozkary.com

CodeProject