title: Efficiently getting a count of child records when returning parent record(s)
body: <p>I am using Entity Framework 4.1 using the Code First approach. I have two entities that exhibit a parent-child relationship. To provide a concrete example, imagine I have a <code>Category</code> entity that has zero-to-many <code>Product</code> entities associated with it. I have set up navigation properties on both sides (in my example, the <code>Category</code> entity would have an <code>ICollection&lt;Product&gt;</code> property while the <code>Product</code> entity has a <code>Category</code> property). </p>
  
  <p>Now, when I get <code>Category</code> entities I want to also get back a <em>count</em> of the number of children records for each category. I am aware I can do:</p>
  
  <pre><code>Category category = dbContext.Categories.Single(...);
  int productCount = category.Products.Count();
  </code></pre>
  
  <p>But I am concerned because the resulting SQL that gets sent to the database depends on whether I use lazy or eager loading. </p>
  
  <p>In the first case (lazy loading), the call to the <code>Products</code> collection prompts EF to run a SQL query like:</p>
  
  <pre><code>SELECT ... all columns ...
  FROM Products
  WHERE CategoryID = @CategoryID
  </code></pre>
  
  <p>In the second case (eager loading), the products are loaded when the category information is retrieved so there is no second query to the database, but the downside is that if I'm not at all interested in products (other than their count) then I'm bringing back a lot of unneeded data.</p>
  
  <p>What I'd like it to have the best of both worlds: namely, the ability to have just one database query and one that uses <code>SELECT COUNT(*)</code> rather than one that gets all of the columns from the table. In short, I'd like SQL like the following to be sent to the database:</p>
  
  <pre><code>SELECT ... all category columns ...,
         (SELECT COUNT(*) FROM Products p WHERE p.CategoryID = c.CategoryID)
  FROM Categories c
  WHERE c.CategoryID = ...
  </code></pre>
  
  <p>Is that at all possible with EF or is what I want a pipe dream?</p>