CODEWITHSUNDEEP
sqlentity-framework
Scott Mitchell
Scott Mitchell

Reputation: 8759

Efficiently getting a count of child records when returning parent record(s)

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 Category entity that has zero-to-many Product entities associated with it. I have set up navigation properties on both sides (in my example, the Category entity would have an ICollection<Product> property while the Product entity has a Category property).

Now, when I get Category entities I want to also get back a count of the number of children records for each category. I am aware I can do:

Category category = dbContext.Categories.Single(...);
int productCount = category.Products.Count();

But I am concerned because the resulting SQL that gets sent to the database depends on whether I use lazy or eager loading.

In the first case (lazy loading), the call to the Products collection prompts EF to run a SQL query like:

SELECT ... all columns ...
FROM Products
WHERE CategoryID = @CategoryID

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.

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 SELECT COUNT(*) 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:

SELECT ... all category columns ...,
       (SELECT COUNT(*) FROM Products p WHERE p.CategoryID = c.CategoryID)
FROM Categories c
WHERE c.CategoryID = ...

Is that at all possible with EF or is what I want a pipe dream?

Upvotes: 4

Views: 2586

Answers (2)

ozkary
ozkary

Reputation: 2704

Yes, this is possible with EF. You can also create a view model to show the information with the child counts as properties. This article cover how to do that.

http://www.ozkary.com/2015/04/entity-framework-associated-table.html

Upvotes: 0

ashic
ashic

Reputation: 6495

Not sure, but maybe try this:

var result = db.Categories.Where(x => x.CategoryId == categoryId)
                          .Select(y => new 
                              { 
                                 Count = y.Products.Count(), 
                                 Category = y
                              })
                          .Single();

//result.Count

//result.Category

Upvotes: 5

Related Questions