EF6 Code First Composite Primary AND Foreign Keys: Foreign Key must be mapped

Question

Well, after a morning of beating my head against the wall, I'm tossing this out there.

I have a DB Table (Table1) with a composite PK (Column1, Column2, Column3). (Column1,Column3) is ALSO a FK to another table (Table2).

Trying to use Code First EF6 (6.1.3) and here's the models:

[Table("DB.Table1")]
public partial class Object1
{
    [Key]
    [Column(Order=0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Column1 { get; set; }

    [Key]
    [Column(Order=1)]
    public byte Column2 { get; set; }

    [Key]
    [Column(Order=2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Column3 { get; set; }

    public virtual Object2 SecondObject { get; set; }
}

[Table("DB.Table2")]
public partial class Object2
{
    public Object2()
    {
        FirstObjects = new HashSet<Object1>();
    }

    [Key]
    [Column(Order=0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Column1 { get; set; }

    [Key]
    [Column(Order=1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Column3 { get; set; }

    public virtual ICollection<Object1> FirstObjects { get; set; }
}

My modelBuilder (Fluent API):

modelBuilder.Entity<Object1>()
    .HasRequired(o => o.SecondObject)
    .WithMany()
    .HasForeignKey(o => new { o.Column1, o.Column3 });

Maybe I'm missing something with the ICollection in Object2?

I'm getting an error that says "Foreign key constraint from table Object1 (Column1, Column3) to table Object2 (Column1, Column3):: Insufficient mapping: Foreign key must be mapped to some AssociationSet or EntitySets participating in a foreign key association on the conceptual side."

I tried:

modelBuilder.Entity<Object1>()
    .HasRequired(o => o.SecondObject)
    .WithMany(o => o.FirstObjects)
    .HasForeignKey(o => new { o.Column1, o.Column3 });

to no avail. I got a "FirstObjects declared on Object2 has been configured with conflicting foreign keys" error.

This was CodeFirst generated from an existing database.

Answer 1

Shortly, your first fluent configutaion

modelBuilder.Entity<Object1>()
    .HasRequired(o => o.SecondObject)
    .WithMany()
    .HasForeignKey(o => new { o.Column1, o.Column3 });

along with the Object2.FirstObjects navigation property creates two additional columns and two foreign keys which is incorrect and will cause problems.

The correct configuration for such model is the second one:

modelBuilder.Entity<Object1>()
    .HasRequired(o => o.SecondObject)
    .WithMany(o => o.FirstObjects)
    .HasForeignKey(o => new { o.Column1, o.Column3 });

But make sure you start with fresh or no database because automatic migration cannot handle that change correctly. If you want to keep your database, then comment out any Object1 or Object2 configuration code and also the respective DbSets. Then update the database. (make sure Table1 and Table2 get deleted). Then uncomment the Object1 and Object2 related code (with the second configuration!) and update the database. Now everything should be fine. I did it in a clean environment and it worked.