Mapping for self referencing entity in EF Code First

Question

In my database I have a table Category, with columns Id, CategoryName, ParentCategoryId, where ParentCategoryId has a constraint on Category.Id.

I'm using entity framework code first, where the entity looks like:

public class Category
{
   public long Id { get; private set; }
   public string CategoryName { get; private set; }
   public long? ParentCategoryId { get; private set; }
   public Category ParentCategory { get; private set; }       
   public virtual ICollection<Category> SubCategories { get; private set; }
}

If I try to run a query against this, I get the exception:

 The relationship 'ComplaintModel.FK_Complaint_Category' was not loaded because the type 'ComplaintModel.Category' is not available.\r\nThe following information may be useful in resolving the previous error:\r\nThe required property 'Category1' does not exist on the type 'EC.Complaint.Services.Command.Domain.Entities.Category'.\r\n\r\n"}    System.Exception {System.Data.MetadataException}

So it seems it needs navigation properties, if I add these:

 public ICollection<Category> Category1 { get; private set; }
 public long? Category2Id { get; private set; }
 public Category Category2 { get; private set; }

the query works.

But of course, I don't want the Category1 and Category2 properties, I want ParentCategory and SubCategories properties being used.

How can I tell code first to use the correct navigation properties?

Very similar question here: http://stackoverflow.com/a/4812413/97803 — David Yates, Jun 27 '16 at 22:49

bdparrish · Accepted Answer · 2015-07-22T11:43:21.480

9

your POCO class should look like this ...

public class Category
{
   public long Id { get; private set; }
   public string CategoryName { get; private set; }
   public long? ParentCategoryId { get; private set; }
   public virtual Category ParentCategory { get; private set; }       
   public virtual ICollection<Category> SubCategories { get; private set; }
}

public class CategoryConfiguration : EntityTypeConfiguration<Category>
{
    public CategoryConfiguration()
    {
        this.HasKey(x => x.Id);

        this.HasMany(category => category.SubCategories)
            .WithOptional(category => category.ParentCategoryId)
            .HasForeignKey(course => course.UserId)
            .WillCascadeOnDelete(false);
    }
}

edited Jul 22 '15 at 11:43

answered Mar 13 '12 at 13:00

bdparrish

3,216
3
37
58

As you suggested I tried 'modelBuilder.Entity().HasMany(category => category.SubCategories).WithRequired(category => category.ParentCategory).HasForeignKey(category => category.Id).WillCascadeOnDelete(false);' and indeed, that also works. I like your approach better, it's strongly typed! Thanks! – L-Four Mar 13 '12 at 13:07
or maybe it should be WithOptional instead of WithRequired...? – L-Four Mar 13 '12 at 13:15
1

@Lud, Yes, sorry it should be WithOptional. – bdparrish Mar 13 '12 at 13:17
Interesting, was having trouble putting it the other way around. e.g. .HasOptional(x=> x.Parent).WithMany(c=>c.Children) , which didn't give desired results , however this works thank you. – John Nov 09 '15 at 07:53

score 4 · Answer 2 · answered Jun 14 '14 at 07:55

Entity Framework 6 handles this. You have to just ensure [Key] annotation is used to identify primary key. Not sure if it works with virtual keyword or not.

 public class Category
{
   [Key]
   public long Id { get; private set; }
   public string CategoryName { get; private set; }
   public long? ParentCategoryId { get; private set; }
   public Category ParentCategory { get; private set; }       
   public ICollection<Category> SubCategories { get; private set; }
}

score 0 · Answer 3 · answered Mar 13 '12 at 12:46

I think I found it, I added the following in the OnModelCreating operation:

 modelBuilder.Entity<Domain.Entities.Category>().HasOptional<Category>(c => c.ParentCategory).WithMany().Map(m => m.MapKey(new string[] { "Id", "ParentCategoryId" }));

and now the ParentCategory and SubCategories properties work (and I could remove Category1 and Category2). Don't know exactly why SubCategories works though...

Mapping for self referencing entity in EF Code First

3 Answers3

Linked