How to set ID to correspondent to object in entity framework

Question

I have Category that can have RootCategory. Problem is that it does not set RootCategoryID properly, instead it is creating Category_ID in database that I don't even made in my model.

It does map everything like I expect it to if I don't have modified get on RootCategory by the way. But then RootCategory is always null (he does not know where to get it from)

Model

public class Category
{
    public int ID { get; set; }
    // Tried [ForeignKey("RootCategoryID")]
    public Category RootCategory {
        get
        {
            ORDataContext _db = new ORDataContext();
            return _db.Categories.Where(x => x.ID == this.RootCategoryID).SingleOrDefault();
        }
    }
    public int? RootCategoryID { get; set; } // This does not set itself properly

    public ICollection<Category> ChildCategories { get; set; }
}

Generated database after update-database

-ID
-RootCategoryID (that I have created, but it's not used)
-Category_ID (taht EF created for me, that I don't want)

Answer 1

You don't need to manuallyload the nav property RootCategory like you have, EF will do it automatically. However, EF is having trouble inferring what you're wanting, you should map it explicitly either with data annotations:

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

      public virtual Category RootCategory { get; set; }
      [ForeignKey("RootCategory")]
      public int? RootCategoryID { get; set; } // This does not set itself properly

      public virtual ICollection<Category> ChildCategories { get; set; }    

   }

or via fluent:

  protected override void OnModelCreating( DbModelBuilder modelBuilder )
  {
     modelBuilder.Entity<Category>()
        .HasMany( c => c.ChildCategories )
        .WithOptional( ca => ca.RootCategory )
        .HasForeignKey( c => c.RootCategoryID );
  }

And all your properties/collections should work.