Effectively getting the number of child records when returning the parent record (s)

Question

I am using Entity Framework 4.1 using the Code First approach. I have two objects that show parent-child relationships. To provide a concrete example, imagine that I have a Category object that is associated with objects with zero Product numbers. I set navigation properties on both sides (in my example, a Category object would have an ICollection<Product> property, and a Product object would have a Category property).

Now that I get Category objects, I also want to return the number of children entries for each category. I know that I can:

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

But I am worried that the resulting SQL query sent to the database depends on whether I use lazy or impatient loading.

In the first case (lazy loading), calling the Products collection calls EF to run the SQL query, for example:

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

In the second case (when loading) the products are loaded when information about the category is received, so there is no second request to the database, but the disadvantage is that if I am not at all interested in the products (except their account), then I return a lot of unnecessary data.

I would like for him to have the best of both worlds: namely, the ability to have only one database query and one that uses SELECT COUNT(*) , and not one that gets all the columns from the table. In short, I would like SQL to be sent to the database as follows:

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

Is this even possible with EF or is this what I want to sleep tubes?

Answer 1

Not sure, but maybe try the following:

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

Answer 2

Yes, it is possible with EF. You can also create a view model to display information based on the number of children as properties. This article describes how to do this.

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