Many to many self join through junction table
- by Peter
I have an EF model that can self-reference through an intermediary class to define a parent/child relationship. I know how to do a pure many-to-many relationship using the Map command, but for some reason going through this intermediary class is causing problems with my mappings. The intermediary class provides additional properties for the relationship. See the classes, modelBinder logic and error below:
public class Equipment
{
[Key]
public int EquipmentId { get; set; }
public virtual List<ChildRecord> Parents { get; set; }
public virtual List<ChildRecord> Children { get; set; }
}
public class ChildRecord
{
[Key]
public int ChildId { get; set; }
[Required]
public int Quantity { get; set; }
[Required]
public Equipment Parent { get; set; }
[Required]
public Equipment Child { get; set; }
}
I've tried building the mappings in both directions, though I only keep one set in at a time:
modelBuilder.Entity<ChildRecord>()
.HasRequired(x => x.Parent)
.WithMany(x => x.Children )
.WillCascadeOnDelete(false);
modelBuilder.Entity<ChildRecord>()
.HasRequired(x => x.Child)
.WithMany(x => x.Parents)
.WillCascadeOnDelete(false);
OR
modelBuilder.Entity<Equipment>()
.HasMany(x => x.Parents)
.WithRequired(x => x.Child)
.WillCascadeOnDelete(false);
modelBuilder.Entity<Equipment>()
.HasMany(x => x.Children)
.WithRequired(x => x.Parent)
.WillCascadeOnDelete(false);
Regardless of which set I use, I get the error: The foreign key component 'Child' is not a declared property on type 'ChildRecord'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property. when I try do deploy my ef model to the database.
If I build it without the modelBinder logic in place then I get two ID columns for Child and two ID columns for Parent in my ChildRecord table. This makes sense since it tries to auto create the navigation properties from Equipment and doesn't know that there are already properties in ChildRecord to fulfill this need.
I tried using Data Annotations on the class, and no modelBuilder code, this failed with the same error as above:
[Required]
[ForeignKey("EquipmentId")]
public Equipment Parent { get; set; }
[Required]
[ForeignKey("EquipmentId")]
public Equipment Child { get; set; }
AND
[InverseProperty("Child")]
public virtual List<ChildRecord> Parents { get; set; }
[InverseProperty("Parent")]
public virtual List<ChildRecord> Children { get; set; }
I've looked at various other answers around the internet/SO, and the common difference seems to be that I am self joining where as all the answers I can find are for two different types.
Entity Framework Code First Many to Many Setup For Existing Tables
Many to many relationship with junction table in Entity Framework?
Creating many to many junction table in Entity Framework
