Mapping a self-join to a collection in code first entity framework 4.3

Question

I have a POCO class that maps to a self-joined table defined like this:

CREATE TABLE [dbo].[GalleryCategories](
    [CategoryID] [int] IDENTITY(0,1) NOT NULL PRIMARY KEY CLUSTERED,
    [Name] [nvarchar](256) NOT NULL,
    [ParentID] [int] NULL REFERENCES [dbo].[GalleryCategories] ([CategoryID]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
)

I know there's a way to define a relationship using the model builder to reference a parent from a child... (e.g. like this)

But the class I'm trying to map looks like this...

public class GalleryCategory
{
    [Key]
    public int CategoryID { get; set; }
    public string Name { get; set; }
    public int? ParentID { get; set; }
    public List<Category> Subcategories { get; set; }
}

In other words, I'm trying to get Subcategories populated with all of the children - i.e. traverse down the hierarchy, not up the hierarchy.

Is there any way to do this using EF? This must be a FAQ somewhere but I couldn't find it after googling for an hour :-)

Answer 1

Should that be a List Subcategories in your class? If so, and it's a self referencing table, you can create the mapping either way Data Annotations like this:

 public class GalleryCategory
    {
        [Key]
        public int CategoryID { get; set; }
        public string Name { get; set; }
        public int? ParentID { get; set; }
        [ForeignKey("ParentID")]
        public virtual List<GalleryCategory> Subcategories { get; set; }
    }

or in Fluent like this:

 public class Model : DbContext
    {
        public DbSet<GalleryCategory> Categories { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<GalleryCategory>()
               .HasMany(x => x.Subcategories)
                .WithOptional()
                .HasForeignKey(g => g.ParentID);
        }
    }