Entity Framework database first aggregate column with eager loading

c# entity-framework entity-framework-6 sql-server


I'm using Entity Framework 6.x database first and a SQL Azure database.

I have a database table of Studies and a separate table of Respondents. In my EF designer, I have a Studies entity and Respondents entity and the Studies Entity has a Navigation Property for Respondents. Each study can have thousands of respondents and there are hundreds of studies.

My problem is that I'm trying to create a calculated column on the Studies table that contains the COUNT of Respondents and I'm trying to eager load this value. The reason that I need eager loading is because I'm populating a DataGrid (WPF) with the list of Studies and one of the columns in my grid is RespondentCount. If I don't eager load this value, then the scrolling on my data grid is janky as the RespondentCounts are lazy loaded when the user scrolls the grid.

I can certainly eager load all the respondents using this statement:

ctx.Studies.Include(s => s.Respondents)

and then do a .COUNT() on the Respondents Navigation property but then I'd be loading thousands of Respondents per Study which is a huge performance hit and a lot of wasted memory because all I need is the COUNT.

What I really need is:

ctx.Studies.Include(s => s.Respondents.Count)

But this doesn't work.

I tried setting up a Database VIEW that contains the Studies table with a simple calculated field for RespondentCount, but the VIEW is readonly in Entity Framework and I lose all my other foreign key relationships on the Studies table when I bring the VIEW into the EF designer.

How can I get this aggregate value eager loaded, without having to load all the respondents?

11/5/2014 5:49:06 PM

Accepted Answer


  1. Create a SQL user-defined function that counts respondents by study.
  2. Create a computed column in your Studies table to use that new function.
  3. In your EF mapping, add that new computed column and set the HasDatabaseGeneratedOption property with a parameter of DatabaseGeneratedOption.Computed.

Some potential code with my own test tables

create table respondents (id int, study_id int)

create function dbo.CountRespondents (@id int)
  returns int
  return (select count(1) from respondents  where study_id = @id)

create table studies (id int, name varchar(50), respondents as dbo.CountRespondents(id))

insert into respondents values (1, 1)
insert into respondents values (2, 1)
insert into respondents values (3, 1)
insert into respondents values (4, 2)
insert into respondents values (5, 2)

insert into studies values (1, 'Study 1')
insert into studies values (2, 'Study 2')

select * from studies

EF Code

ctx.Studies.Property(s => s.Respondents).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

Instead of Include, use Property.

I can't really say if this would be performant enough for you since I don't know the volume - and I haven't actually tested the EF portion with the SQL I wrote. But I would imagine it should work fine.

11/5/2014 8:07:08 PM

Popular Answer


Sometimes it is useful to know how many entities are related to another entity in the database without actually incurring the cost of loading all those entities. The Query method with the LINQ Count method can be used to do this. For example:

using (var context = new BloggingContext()) 
    var blog = context.Blogs.Find(1); 

    // Count how many posts the blog has  
    var postCount = context.Entry(blog) 
                          .Collection(b => b.Posts) 

Related Questions


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