Entity Framework 4.1 Retrieving self referencing data

Question

I am using Entity Framework 4.1 code first and ASP.NET MVC 3 and I am struggling to get my self referencing setup properly. I have a Category class. It must be self referencing to itself. A category can be a parent category when the ParentCategoryId in the table is null. If a category has a ParentCategoryId with a value then it means that it belongs to a parent category.

I followed this article on Code Project.

Here is my Category class:

public class Category
{
     public int Id { get; set; }
     public string Name { get; set; }
     public string Description { get; set; }
     public string MetaKeywords { get; set; }
     public string MetaDescription { get; set; }
     public bool IsActive { get; set; }
     public virtual Category ParentCategory { get; set; }
     public int? ParentCategoryId { get; set; }
}

My context class:

public class PbeContext : DbContext
{
     public DbSet<Category> Categories { get; set; }

     protected override void OnModelCreating(DbModelBuilder dbModelBuilder)
     {
          dbModelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

          dbModelBuilder.Entity<Category>()
               .HasOptional(c => c.ParentCategory)
               .WithMany()
               .HasForeignKey(p => p.ParentCategoryId);
     }
}

Not sure if the above is correct?

Can someone please help me get this right? What I need is that when I query a category by id then it must bring back the parent category (only loaded when needed). Also it must load any child categories (only when needed). I have not yet added a list to the category class for the child categories.

What would the above quesries look like to retrieve a category with it parent category and child categories?

EDIT

This is how I retrieve my category:

public Category GetById(int id)
{
     return db
          .Categories
          .Find(id);
}

Because the ParentCategory reference can be null, how would I display this in the view? I have the following:

@Model.ParentCategory.Name

..but won't it give an error if the category has no parent category associated with it? How would I display it in the view?

Answer 1

If you need to access child categories you can add a collection property to your model

public class Category
{
     public int Id { get; set; }
     public string Name { get; set; }
     public string Description { get; set; }
     public string MetaKeywords { get; set; }
     public string MetaDescription { get; set; }
     public bool IsActive { get; set; }
     public virtual Category ParentCategory { get; set; }
     public int? ParentCategoryId { get; set; }
     public virtual ICollection<Category> ChildCategories{ get; set; }
}

Then you can setup the model as

      dbModelBuilder.Entity<Category>()
           .HasOptional(c => c.ParentCategory)
           .WithMany(c => ChildCategories)
           .HasForeignKey(p => p.ParentCategoryId);

Edit

You should check whether ParentCategory is null or not.

@if(Model.ParentCategory != null){
  <div>@Model.ParentCategory.Name</div>
}