Many to many self join through junction table

Question

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

Answer 1

If you define this classes:

public class Equipment
{
    [Key]
    public int EquipmentId { get; set; }

    public virtual List<EquipmentRelation> Parents { get; set; }
    public virtual List<EquipmentRelation> Children { get; set; }
}

public class EquipmentRelation
{
    [Key]
    [Column("ChildId", Order=1)]
    public int ChildId { get; set; }

    [Key]
    [Column("ParentId", Order=2)]
    public int ParentId { get; set; }

    [Required]
    public int Quantity { get; set; }

    [Required]
    public Equipment Parent { get; set; }

    [Required]
    public Equipment Child { get; set; }
}

Code First will infer correctly the relationships between an equipment piece and its parent and children. Parent and children are both optional. The EquipmentRelation has required parent and child.

The difference with your original code is that the association table contains a composite key with the PK for both the parent and child equipment. (I've renamed it to EquipmentRelation).

If you want to fine tune it, or "code document" it, you can, of course, use Fluent API to express the inferred relationships.

NOTE: the Column attribute is neccessary because when you define a composite key using attributes, EF needs to know the order of the columns in the key. Alternatively, you can define this key using Fluent API HasKey