Entity Framework Code First Multi Column Foreign Key

Question

Using code first I designed 3 classes:

class User {     

 public Int32 ID {get;set;}  

 public virtual ICollection<UserCityDetail> {get;set;}

 public Int32 MainCityID {get;set;}
 public UserCityDetail MainCityDetail {get;set;}

}

class City{

 public Int32 ID {get;set;}

 ...
}

class UserCityDetail{

 [Key, Column(Order = 0)]
 public Int32 UserID {get;set;}

 [Key, Column(Order = 1)]
 public Int32 CityID{get;set;}

 ...
}

So basically I have a user that have different details on several cities. User ID is both PK and FK to UserCityDetail. I also want to have a direct reference to the main city details, so I've put a city ID FK on User.

How to configure User ID and MainCityID to act like FK for MainCityDetail?

Answer 1

Since there are two relationships between User and UserCityDetail EF has a problem of identifying which navigational properties belong to which relationship. Use the fluent API to map the relationships.

There would be another problem if you are using SQL Server because two relationships leads to multiple cascade delete paths. So you have to make MainCityDetail an optional relationship.

class User {     

 public Int32 ID {get;set;}  

 public virtual ICollection<UserCityDetail> {get;set;}

 public int? MainCityUserID {get;set;}
 public int? MainCityID {get;set;}

 public UserCityDetail MainCityDetail {get;set;}

}


public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>()
            .HasOptional(u => u.MainCityDetail)
            .WithMany()
            .HasForeignKey(u => new { u.MainCityUserID, u.MainCityID})
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<User>()
            .HasMany(u => u.Cities)
            .WithRequired(d => d.User)
            .HasForeignKey(d => d.UserId);
    }
}

For further details on fluent mappings check here.