2 models with one to many relationship - How to list the count of items in the first model

Question

I had a tough time coming up with a good title. I am working on an MVC application using asp.net and Entity Framework. I have 2 models, Categories and Products. There can be many products in each category. I have a page that lists out each category and each product with the associated fields for each. I want to add a column(field) to the Category list that lists how many products are in that category. I have no idea how to go about doing this. I am guessing I will need to add an additional variable to the Categories model called "productCount" or something like that.

Category model:

public class CategoryModel
{
    public int ID { get; set; }

    [Required(ErrorMessage = "Required")]
    [Display(Name = "Category Name")]
    [MaxLength(50)]
    public String categoryName { get; set; }

    [DefaultValue(true)]
    [Display(Name = "Active?")]
    public bool isActive { get; set; }

}

Product model:

public class ProductModel
{
    public int ID { get; set; }

    [Required(ErrorMessage = "Required")]
    [Index("ItemNumber", 1, IsUnique = true)]
    [Display(Name = "Item #")]
    public int itemNumber { get; set; }

    [Required(ErrorMessage = "Required")]
    [Display(Name = "Product")]
    [MaxLength(50)]
    public String product { get; set; }

    [Required(ErrorMessage = "Required")]
    [Display(Name = "Description")]
    [MaxLength(500)]
    public String description { get; set; }

    [DefaultValue(true)]
    [Display(Name = "Active?")]
    public bool active { get; set; }

    [Display(Name = "Image Name")]
    public String imageName { get; set; }

    [Display(Name = "PDF Name")]
    public String PDFName { get; set; }

    [ForeignKey("Category")]
    public int CategoryID { get; set; }

    public virtual CategoryModel Category { get; set; }

    public IEnumerable<SelectListItem> CategoryList { get; set; }

    public static IEnumerable<SelectListItem> getCategories()
    {
        using (var db = new ProductContext())
        {
            List<SelectListItem> list = new List<SelectListItem>();
            var x = db.Categories.ToList();
            foreach (var y in x)
            {
                list.Add(new SelectListItem { Value = y.ID.ToString(), Text = y.categoryName });
            }
            return list;
        }

    }

}

Admin Model:

public class AdminModel
    {
        public IEnumerable<CategoryModel> Categories { get; set; }
        public IEnumerable<ProductModel> Products { get; set; }
        public RegisterViewModel RegisterUsers { get; set; }
    }

And here is the Controller method that lists the categories:

public ActionResult ControlPanel()
    {
        ViewBag.Message = TempData["Message"] == null ? "" : TempData["Message"].ToString();
        //using (var db = new )
        using (var db = new ProductContext())
        {
            var categories = from c in db.Categories
                           select c;
            categories = categories.OrderByDescending(c => c.isActive);
            var model = new AdminModel
            {
                Categories = categories.ToList(),
                Products = db.Products.ToList()
            };

            return View(model);
        }
    }

Answer 1

For a single categoryID you can just use Linq's Count():

int specificCategoryID = 15;
return db.Products.Where(w => w.CategoryID == specificCategoryID).Count();

For a list of key/value pairs of categoryIDs and the count of products, something like this:

var products = db.Products.AsEnumerable();
var productCount = products.GroupBy(p => p.CategoryID, 
                (k, v) => new { CategoryID = k, ProductCount = v.Count() });    

I would recommend using a proper viewmodel and making sure that it only uses one query so you dont run into a select n+1 issue.

Edit: Assuming you always have a list of all categories and products you can just do the calculation in the Admin ViewModel on the items you already pulled from the DB:

public class AdminModel
{
    public IEnumerable<CategoryModel> Categories { get; set; }
    public IEnumerable<ProductModel> Products { get; set; }
    public RegisterViewModel RegisterUsers { get; set; }

    public int GetProductCountForCategoryID(int categoryID)
    {
        return this.Products
            .Count(w => w.CategoryID == categoryID);
    }
}

Then in the view, just pass in whatever category ID like this:

@Model.GetProductCountForCategoryID(categoryID);