ihisham ihisham - 1 month ago 8
C# Question

Entity framework generate extra foreign keys in the database

I have the following classes, it s all good. But in the Table generated in Database i found in the AB table: Id, AId, BId. how it s supposed to be and two additional column that shouldnt be there AId1 & AId2 that they are also foreign keys

class A

public class A
{
public int Id { get; set; }
ICollection<AB> ABs { get; set; }
}


class B

public class B
{
public int Id { get; set; }
ICollection<AB> ABs { get; set; }
}


class AB

public class AB
{
public int Id { get; set; }
public A A { get; set; }
public B B { get; set; }
public int AId { get; set; }
public int BId { get; set; }
}


ABMapping

public class ABMap : EntityTypeConfiguration<AB>
{
public ABMap()
{
this.HasKey(a => a.Id);

this.HasRequired(e => e.A)
.WithMany()
.HasForeignKey(e => e.AId)
.WillCascadeOnDelete(false);

this.HasRequired(c => c.B)
.WithMany()
.HasForeignKey(e => e.BId)
.WillCascadeOnDelete(false);

this.Property(e => e.AId)
.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_AB", 1) { IsUnique = true }));
this.Property(e => e.BId)
.HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_AB", 2) { IsUnique = true }));
}
}


Context

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//...
modelBuilder.Configurations.Add(new ABMap());
//...
}

Answer

You need to specify the collection property when you call WithMany. Because you don't tell EF the two ABs collections are part of the relationships you define then two additional One to Many relationships are created. Change your fluent API mapping to the following.

public class ABMap : EntityTypeConfiguration<AB>
{
    public ABMap()
    {
        this.HasKey(a => a.Id);

        this.HasRequired(e => e.A)
            .WithMany(a => a.ABs)
            .HasForeignKey(e => e.AId)
            .WillCascadeOnDelete(false);
        this.Property(a => a.AId);

        this.HasRequired(c => c.B)
            .WithMany(b => b.ABs)
            .HasForeignKey(e => e.BId)
            .WillCascadeOnDelete(false);
        this.Property(a => a.BId);

        this.Property(e => e.AId)
                .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_AB", 1) { IsUnique = true }));
        this.Property(e => e.BId)
                .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_AB", 2) { IsUnique = true }));
    }
}
Comments