1

I have a table which links to itself in a many to many self join. The tables are created correctly but Entity Framework is adding an additional Entity_ID field in the link table.

CREATE TABLE dbo.LinKTable
(
    EntityAID int NOT NULL,
    EntityBID int NOT NULL,
    Entity_ID int NULL,         <-- This should not be here.
    Active bit NOT NULL
)

Where is this erroneous link / column coming from!?

POCO Declarations

Entity

public int ID { get; set; }
public virtual ICollection<LinkTable> links { get; set; }

LinkTable

public int EntityAID { get; set; }
public int EntityBID { get; set; }
public Entity EntityA { get; set; }
public Entity EntityB { get; set; }
public bool Active { get; set; }

DBContext

modelBuilder.Entity<Entity>().ToTable("Entity", "dbo").HasKey(am => new { am.EntityAID, am.EntityBID });
modelBuilder.Entity<Entity>().HasRequired(am => am.EntityA).WithMany().HasForeignKey(am => am.EntityAID).WillCascadeOnDelete(false);
modelBuilder.Entity<Entity>().HasRequired(am => am.EntityB).WithMany().HasForeignKey(am => am.EntityBID).WillCascadeOnDelete(false);

Edit:

enter image description here

This is what I am trying to model. An entity links to another entity with an additional attribute of active. It is a many to many relationship (An entity can link to many other entities) with an additional attribute.

Three Value Logic
  • 1,102
  • 1
  • 15
  • 37
  • Do you have an edmx file in the project? If so can I see a picture of it? – Ciaran Donoghue May 19 '15 at 11:31
  • I am using code first so no edmx. I have tried to model what I am doing in the database. – Three Value Logic May 19 '15 at 11:43
  • I think it is to do with entity framework fixing the many to many relationship in SQL itself, because I have used database first approach and In SQL or whatever database you are using. I added additional tables to fix two many to many relationships but in the edmx file it only showed many to many tables instead of the fixed one to many tables. So it might be because in order for it to save correctly in the database it adds it – Ciaran Donoghue May 19 '15 at 11:51

1 Answers1

2

I think the problem is because you are not using the links navigation property that is in your Entity class in the configuration of one of the one-to-many relationships. EF is creating implicitly another one-to-many relationship (that is unidirectional in this case). That's way it adds a FK column in your LinkTable as part of that relationship.

You should use the links navigation property and add a new one to configure properly both one-to-many relationships that you already have:

public class Entity
{
  public int ID { get; set; }
  public virtual ICollection<LinkTable> Links { get; set; }
  // Change the name what you want
  public virtual ICollection<LinkTable> AnotherLinks { get; set; }
}

Then in the OnModelCreating method on your Context do this:

modelBuilder.Entity<LinkTable>().HasRequired(am => am.EntityA).WithMany(e=>e.Links).HasForeignKey(am => am.EntityAID).WillCascadeOnDelete(false);
modelBuilder.Entity<LinkTable>().HasRequired(am => am.EntityB).WithMany(e=>e.AnotherLinks).HasForeignKey(am => am.EntityBID).WillCascadeOnDelete(false);
ocuenca
  • 38,548
  • 11
  • 89
  • 102