title: Entity Framework database first aggregate column with eager loading
body: <p>I'm using Entity Framework 6.x database first and a SQL Azure database.</p>
  
  <p>I have a database table of <strong>Studies</strong> and a separate table of <strong>Respondents</strong>.  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.</p>
  
  <p>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.</p>
  
  <p>I can certainly eager load all the respondents using this statement:</p>
  
  <pre><code>ctx.Studies.Include(s =&gt; s.Respondents)
  </code></pre>
  
  <p>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.  </p>
  
  <p>What I really need is:</p>
  
  <pre><code>ctx.Studies.Include(s =&gt; s.Respondents.Count)
  </code></pre>
  
  <p>But this doesn't work.</p>
  
  <p>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.</p>
  
  <p>How can I get this aggregate value eager loaded, without having to load all the respondents?</p>