Entity Framework creating unwanted column

Question

I have a project class which contains:

int ProjectID;
string ProjectName;
List<Item> Items;

I have a item class which contains:

int ItemID;
int? ParentID; // ID of Parent Item
string ItemName;
List<Item> Items;

I would expect my Items Table to hold the following columns: ItemID, ParentID, ItemName

But for some reason it's adding another column ItemItemID.

I have tried several things with fluent API. (WithOptional, MapKey etc...) But I can't find a thing that is working for me. I think the problem is that the ParentID is not seen as the relation for the Item.

Please show me a solution, cause I'm already stuck for hours...

Answer 1

Entity Framework could not infer that the ParentID property is actually the foreign key to the parent Item object in the same table, so it decided to create a special column for that in the database. You have to tell it that ParentID is actually a foreign key by using the ForeignKey attribute.

Change the definition of the Item class to something like this:

public class Item
{
    public Item()
    {
        Items = new HashSet<Item>();
    }

    public int ItemID { get; set; }
    [ForeignKey("Parent")]
    public int? ParentID { get; set; } // ID of Parent Item
    public string ItemName { get; set; }
    public virtual ICollection<Item> Items { get; set; }
    public virtual Item Parent { get; set; }
}

Notice how I added a public virtual Item Parent property because the ForeignKey attribute needs to point to the property that presents the parent entity.

If you prefer to use the fluent API, then remove the ForeignKey attribute, and override the OnModelCreating method on the context class to something like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Item>()
        .HasMany(e => e.Items)
        .WithOptional(e => e.Parent)
        .HasForeignKey(e => e.ParentID);
}