Entity Framework modelling one-to-many relationship

Question

I don't know how to configure following relations in EF:

Imagine I need to create some language dictionary model. I have an item (word for example) in one language and in another language. There is some relation between these two items.

For instance: "hund" (German) -> "dog" (English), relation type is "Translate"

public enum Language
{
    English,
    German,
}

public class Item
{
    public long ID { get; set; }
    [Required]
    public string Value { get; set; }
    [Required]
    public Language Language { get; set; }

    public virtual ICollection<ItemRelation> ItemRelations { get; set; }
}

public enum ItemRelationType
{
    Translate,
    Synonym,
}

public class ItemRelation
{
    public long ID { get; set; }

    [ForeignKey("ItemID")]
    public Item Item { get; set; }

    [ForeignKey("RelativeItemID")]
    public Item RelativeItem { get; set; }

    [Required]
    public ItemRelationType Type { get; set; }
}

EF standard migration throw some error in one case or creates columns or FKs I don't wont (Item_ID etc.) in the other.

I guess I need to configure some fluent api - but I am not sure how...

Answer 1

You are missing the actual FK fields ItemID and RelativeItemID:

And to configure the navigation property you can use InverseProperty attribute, along with disabling a bit of EF convention(shown below).

public class ItemRelation
{
   public long ID { get; set; }

   public long ItemID { get; set; } // Missing 
   [ForeignKey("ItemID")]
   [InverseProperty("ItemRelations")]
   public virtual Item Item { get; set; }

   public long RelativeItemID { get; set; } // Missing 
   [ForeignKey("RelativeItemID")]
   [InverseProperty("RelativeItemRelations")]
   public virtual Item RelativeItem { get; set; }

   [Required]
   public ItemRelationType Type { get; set; }
}

The declaration above uses virtual so that lazy loading works. It's not necessary and you can remove it. The consequence is that lazy loading won't work, which is ok too.

Assuming you want a navigation property for the second relation you need to add the property:

public class Item
{
...
   public virtual ICollection<ItemRelation> RelativeItemRelations { get; set; }
...
}

And then disable the cascading delete convention by overriding OnModelCreating, if you haven't already, in your context class as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder) 
{
...
   modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
...

This solution should work but it is effectively disabling cascade delete to all one to many relationships. The upside is that you can get it back on a case by case basis by using fluent api.

The second way to achieve what you want is to just use fluent api as follows:

Add the second navigation property to your Item entity:

public class Item
{
...
   public virtual ICollection<ItemRelation> RelativeItemRelations { get; set; }
...
}

ItemRelation entity is missing the FKs, so here it is:

public class ItemRelation { public long ID { get; set; }

   public long ItemID { get; set; } // Missing 
   public virtual Item Item { get; set; }

   public long RelativeItemID { get; set; } // Missing 
   public virtual Item RelativeItem { get; set; }

   [Required]
   public ItemRelationType Type { get; set; }
}

And to configure the navigation property, and avoid the cascading issue, you just define the relationship using fluent api:

public TheContext : DbContext
{
   public DbSet<Item> Items { get; set; }
   public DbSet<ItemRelation> ItemRelations { get; set; }

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
      base.OnModelCreating(modelBuilder);

      modelBuilder.Entity<ItemRelation>()
                .HasRequired(e => e.Item)
                .WithMany(t => t.ItemRelations)
                .HasForeignKey(e => e.ItemID)
                .WillCascadeOnDelete(false);

      modelBuilder.Entity<ItemRelation>()
                .HasRequired(e => e.RelatedItem)
                .WithMany(t => t.RelativeItemRelations)
                .HasForeignKey(e => e.RelativeItemID)
                .WillCascadeOnDelete(false);       


      // Uncomment the following if you want to disable all cascading deletes and automatic fk creation conventions
      // modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
      // modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
      // modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

    ...
  }
}

Read here for an opinion on why you might consider disabling those conventions.

Answer 2

I think you might be able to get away with this:

public class ItemRelation
{

    public long Id { get; set; }

    [ForeignKey("PrimaryItemId")]
    public Item Item { get; set; }
    public long PrimaryItemId { get; set; }

    [ForeignKey("RelatedItemId")]
    public Item RelatedItem { get; set; }
    public long RelatedItemId { get; set; }

    public ItemRelationType RelationType;
}

Notice that this class now has TWO relations to the Item entity, resulting in two foreign keys. Notice that each Item property has a [ForeignKey] attribute, with the string argument specifying the long to use as the foreign key column.

View this answer as a nudge on a different track. Research this topic more to see if it fits your use case.