Entity Framework: Many To Many Count and Sum

Question

I am using the Entity Framework. I have a many to many relationship Articles <-> Categories. Each article can belong to many categories, and each category can belong to more than one article. I can get the COUNT of articles within each category:

public IEnumerable<MenuCategory> GetMenuCategories()
{
   return (from c in db.CategorySet.Include("Articles")
     orderby c.CategoryName
     select new MenuCategory
     {
        CategoryID = c.CategoryID,
        CategoryName = c.CategoryName,
        CountOfCategory = c.Articles.Count()
      });

}

But I also need to get the total number of articles so that I can calculate what percentage of articles is in each category. Can anyone tell me how to do that by extending the code above and without making a separate call to the database?

Thanks

Answer 1

The code needs to know the total number of articles in order to do this.

If you want to avoid a second database call then you can do the calc in the database and return it as an extra column in the result set. There are different ways to acheive this but one would be to use a view and have a SQL function TotalArticles, then your calc is just articleCount / TotalArticles. I don't know much about the entity-framework so I'm not sure if this is workable.

If you can't do the calc in the database then I imagine the second database call is unavoidable although it is just a count of articles so hardly adding much overhead. You could make this call before the main call and then add the value to your results in a similar manner to above, i.e. PercentageOfArticles = c.Articles.Count() / articleCount