First Multi Column Foreign Key in Entity Framework

c# ef-code-first entity-framework sql

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?

1
5
5/1/2012 10:36:29 AM

Popular Answer

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.

8
5/1/2012 11:04:25 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow