CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6
Dave Weisberg
Dave Weisberg

Reputation: 143

Entity Framework database first aggregate column with eager loading

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?

Upvotes: 1

Views: 1263

Answers (1)

mccee
mccee

Reputation: 1667

Steps:

  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
as
begin
  return (select count(1) from respondents  where study_id = @id)
END

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.

Upvotes: 2

Related Questions