Entity Framework Mapping Many-to-Many Self Relationship with Separate Table

Question

I have an entity that excludes entities of the same type under certain conditions. In order to achieve this, I have an entity class like:

public class Entity
{
    public int ID { get; set; }
    public virtual ICollection<EntityExcludedEntity> ExcludedEntities { get; set; }
}

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

    [Timestamp]
    public byte[] RowVersion { get; set; }

    public int EntityID { get; set; }
    public virtual Entity Entity { get; set; }

    public int ExcludedEntityID { get; set; }
    public virtual Entity ExcludedEntity { get; set; }
}

//declared in the ExcludedEntity mapping class.
public ExcludedEntityMapping()
{
    HasRequired(t => t.Entity).WithMany(t => t.ExcludedEntity).HasForeignKey(t => t.EntityID)
    HasRequired(t => t.ExcludedEntity).WithMany(t => t.ExcludedEntity).HasForeignKey(t => t.ExcludedEntityID);
}

This causes in EF creating a third column and foreign key field called Entity_ID in my model. Seems like it thinks I have another relationship defined here but I don't understand why.

Here is the part related to foreign keys in the tables created:

.ForeignKey("dbo.Entities", t => t.EntityID)
.ForeignKey("dbo.Entities", t => t.ExcludedEntityID)
.ForeignKey("dbo.Entities", t => t.Entity_ID)

By `WithRequiredDependent()` you say that `Entity` has a required `ExcludedEntity`. It should be `HasMany(t => t.ExcludedEntities)`. — Gert Arnold, Apr 22 '15 at 20:35
why this is M:M relationship, and where is your separate table? Can you explain the relationship between the entitites verbally? — renakre, Apr 22 '15 at 22:22
@erkaner One entity can exclude many different entities and that entity itself can be excluded by many other entities. — Farhad Alizadeh Noori, Apr 23 '15 at 13:40
@GertArnold Yes you are absolutely right. I fixed that in the question. However, that was not really the main problem. — Farhad Alizadeh Noori, Apr 23 '15 at 14:12

score 0 · Answer 1 · edited May 23 '17 at 12:22

This post helped me find the answer.

Basically, EF cannot have two foreign keys to the same entity field. If you need to create two foreign key to the same entity you should bind them to different fields. So in this example:

public class Entity
{
    public int ID { get; set; }
    public virtual ICollection<EntityExcludedEntity> ExcludingEntities { get; set; }
    public virtual ICollection<EntityExcludedEntity> ExcludedFromEntities { get; set; }
}

and this configuration:

public DBConceptAnswerExcludedAnswerMapping()
{
    HasRequired(t => t.Entity).WithMany(t => t.ExcludingEntities).HasForeignKey(t => t.EntityID);
    HasRequired(t => t.ExcludedEntity).WithMany(t => t.ExcludedFromEntities).HasForeignKey(t => t.ExcludedEntityID);
}

would solve the problem.

Entity Framework Mapping Many-to-Many Self Relationship with Separate Table

1 Answers1