Entity Framework Code First 6.1.3 - Migration creates incorrect foreign key

Question

There are two independent entities (Source and Target) and an optional entity that joins one Source to one Target. The relationship should look like this, except with navigation properties of type SourceTargetBinding on both the Source and Target entities:

The model:

public class Source
{
    [Key]
    public long SourceID { get; set; }
    public string Name { get; set; }
    public SourceTargetBinding TargetBinding { get; set; }
}

public class Target
{
    [Key]
    public long TargetID { get; set; }
    public string Name { get; set; }
    public SourceTargetBinding SourceBinding { get; set; }
}
public class SourceTargetBinding
{
    [Key]
    public long SourceID { get; set; }
    public long TargetID { get; set; }
    public Source Source { get; set; }
    public Target Target { get; set; }
}

The context:

public class MyContext : DbContext
{
    public MyContext() : base("name=MyContextData")
    {
    }

    public DbSet<Source> Sources { get; set; }
    public DbSet<Target> Targets { get; set; }
    public DbSet<SourceTargetBinding> SourceTargetBindings { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SourceTargetBinding>()
            .HasKey(b => b.SourceID);
        modelBuilder.Entity<SourceTargetBinding>()
            .HasRequired(b => b.Source)
            .WithOptional(s => s.TargetBinding);
        modelBuilder.Entity<SourceTargetBinding>()
            .HasRequired(b => b.Target)
            .WithOptional(t => t.SourceBinding);

        base.OnModelCreating(modelBuilder);
    }
}

And here is the generated migration. Note the incorrect foreign key from SourceTargetBindings to Targets:

    public override void Up()
    {
        CreateTable(
            "dbo.Sources",
            c => new
                {
                    SourceID = c.Long(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.SourceID);

        CreateTable(
            "dbo.SourceTargetBindings",
            c => new
                {
                    SourceID = c.Long(nullable: false),
                    TargetID = c.Long(nullable: false),
                })
            .PrimaryKey(t => t.SourceID)
            .ForeignKey("dbo.Sources", t => t.SourceID)
            .ForeignKey("dbo.Targets", t => t.SourceID)
            .Index(t => t.SourceID);

        CreateTable(
            "dbo.Targets",
            c => new
                {
                    TargetID = c.Long(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.TargetID);
    }

If I delete the navigation properties from source (Source.TargetBinding) and target (Target.SourceBinding), then EF creates the correct foreign key. But I need those navigation properties.

I don't see what I'm doing wrong. It seems to me that TargetID would be the obvious dependent column for the FK. If there are fluent API semantics to specify the dependent column, I can't find it. Map() doesn't work because TargetID is exposed as a property in the model (which I also need).

I have also tried the reverse semantics -- HasOptional-WithRequired on Source and Target instead of HasRequired-WithOptional on the join entity. I get the same incorrect result.

Steve Greene Steve Greene · Accepted Answer · 2016-10-27T01:44:39

The problem is with your junction table. You need to define a composite key since you may have many SourceIds, so SourceId + TargetId would be unique and thus the key:

public class SourceTargetBinding
{
    [Key, Column(Order = 0)]
    public long SourceID { get; set; }
    [Key, Column(Order = 1)]
    public long TargetID { get; set; }
    public Source Source { get; set; }
    public Target Target { get; set; }
}

And your fluent code becomes:

modelBuilder.Entity<SourceTargetBinding>().HasKey(e => new { e.SourceId, e.TargetId });

EDIT: If you want SourceTargetBinding 0:1, then it needs to be marked with both key and FK:

public class SourceTargetBinding
{
    [Key, ForeignKey("Source")]
    public long SourceID { get; set; }
    public long TargetID { get; set; }
    public Source Source { get; set; }
    public Target Target { get; set; }
}

EDIT 2:

    modelBuilder.Entity<SourceTargetBinding>()
        .HasRequired(b => b.Target)
        .WithOptional(t => t.SourceBinding)
        .Map(m => m.MapKey("TargetId"));

Entity Framework Code First 6.1.3 - Migration creates incorrect foreign key

1 Answers