Working with a Model class that has a foreign/navi

Question

I am trying to develop a catalog project in ASP.NET MVC 3 and using EF Code first with an existing Database. There is a Categories table in my database that points to itself. For that, I have written the following model class. --"Correct me if the model is wrong"--

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public int? ParentCategoryID { get; set; }
    public string CategoryDesc { get; set; }

    [ForeignKey("ParentCategoryID")]
    public virtual Category ParentCategory { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

Question : I am unable to understand as to how can i work with this class. While using and passing the following code to the view

var cat = dbStore.Categories.Include("ParentCategory").ToList().

I got this error : Object reference not set to an instance of an object. This is happening because the root category has null ParentCategoryID. Please tell me how will you work with this code or any resource that can help me understand working in such scenarios. Just any sort of code will be helpful that uses the above the model, like displaying a list or a menu or anything, just anything.

Answer 1

Usually what you do is travel from top level categories to bottom level categories. Inorder to do that first you need to define SubCategories collection in your class

public class Category
{
    public int CategoryID { get; set; }
    public string CategoryName { get; set; }
    public int? ParentCategoryID { get; set; }
    public string CategoryDesc { get; set; }

    [ForeignKey("ParentCategoryID")]
    public virtual Category ParentCategory { get; set; }

    [InverseProperty("ParentCategory")]
    public virtual ICollection<Category> SubCategories{ get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

Then you retrieve the top level categories

var topCategories = dbStore.Categories
   .Where(category => category.ParentCategoryID == null)
   .Include(category => category.SubCategories).ToList();

After that you can traverse the hierachey

foreach(var topCategory in topCategories)
{
    //use top category
    foreach(var subCategory in topCategory.SubCategories)
    {

    }

}

Answer 2

If you do not have very many categories you can solve this by loading the whole collection of categories. I think EF will handle the fixup for you so all relations are properly populated.

As far as I know there are no SQL'ish databases/ORM's that can handle this scenario well. An approach I often use is to load the whole collection as I said above and then manually fix the relations. But I do think EF will do that for you.

Basically you should do:

var topCategories = dbStore.Categories.ToList().Where(category => category.ParentCategoryID == null);