Defining multiple Foreign Key for the Same table in Entity Framework Code First

Question

I have two entities in my MVC application and I populated the database with Entity Framework 6 Code First approach. There are two city id in the Student entity; one of them for BirthCity, the other for WorkingCity. When I define the foreign keys as above an extra column is created named City_ID in the Student table after migration. Id there a mistake or how to define these FKs? Thanks in advance.

Student:

public class Student
{
    public int ID { get; set; }

    public string Name { get; set; }

    public string Surname { get; set; }

    public int BirthCityID { get; set; }

    public int LivingCityID { get; set; }


    [ForeignKey("BirthCityID")]
    public virtual City BirthCity { get; set; }

    [ForeignKey("LivingCityID")]
    public virtual City LivingCity { get; set; }
}

City:

public class City
{
    public int ID { get; set; }

    public string CityName { get; set; }


    public virtual ICollection<Student> Students { get; set; }
}

Cicatrix

I simplified my code for clarity, but my code exactly similar logic to this. On the other hand I had already tried to recreate the database from scratch 2-3 time but now I am doing the same again and inform you in a few minutes. – Cicatrix

@ChrisPratt: Here are the steps I followed now: 1) Deleted Migration folder and its content from my project. 2) Clean and Rebuild all the project in the solution. 3) enable-migrations on Package Manager console and then add AutomaticMigrationsEnabled = true; and AutomaticMigrationDataLossAllowed = true; to teh Configuration file's constructor. 4) Add-Migration. >>> – Cicatrix

@ChrisPratt: But after this step I have observed again there is a property "City_ID = c.Int()," in the Up method. However, I have searched in the solution and there is no "City_ID" property. So, why this undefined propety was added to the project? Thanks in advance. – Cicatrix

Possible duplicate of Entity Framework Code First - two Foreign Keys from same table – Joist

Answer 1

To achieve what you want you need to provide some aditional configuration.Code First convention can identify bidirectional relationships, but not when there are multiple bidirectional relationships between two entities.You can add configuration (using Data Annotations or the Fluent API) to present this information to the model builder. With Data Annotations, you’ll use an annotation called InverseProperty. With the Fluent API, you’ll use a combination of the Has/With methods to specify the correct ends of these relationships.

Using Data Annotations could be like this:

public class Student
{
  public int ID { get; set; }

  public string Name { get; set; }

  public string Surname { get; set; }

  public int BirthCityID { get; set; }

  public int LivingCityID { get; set; }


  [ForeignKey("BirthCityID")]
  [InverseProperty("Students")]
  public virtual City BirthCity { get; set; }

  [ForeignKey("LivingCityID")]
  public virtual City LivingCity { get; set; }
}

This way you specifying explicitly that you want to relate the BirthCity navigation property with Students navigation property in the other end of the relationship.

Using Fluent Api could be like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<Student>().HasRequired(m => m.BirthCity)
                                 .WithMany(m => m.Students).HasForeignKey(m=>m.BirthCityId);
     modelBuilder.Entity<Student>().HasRequired(m => m.LivingCity)
                                 .WithMany().HasForeignKey(m=>m.LivingCityId);
}

With this last solution you don't need to use any attibute.

Now, the suggestion of @ChristPratt in have a collection of Student in your City class for each relationship is really useful. If you do that, then the configurations using Data Annotations could be this way:

public class Student
{
  public int ID { get; set; }

  public string Name { get; set; }

  public string Surname { get; set; }

  public int BirthCityID { get; set; }

  public int LivingCityID { get; set; }


  [ForeignKey("BirthCityID")]
  [InverseProperty("BirthCityStudents")]
  public virtual City BirthCity { get; set; }

  [ForeignKey("LivingCityID")]
  [InverseProperty("LivingCityStudents")]
  public virtual City LivingCity { get; set; }
}

Or using Fluent Api following the same idea:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<Student>().HasRequired(m => m.BirthCity)
               .WithMany(m => m.BirthCityStudents).HasForeignKey(m=>m.BirthCityId);
     modelBuilder.Entity<Student>().HasRequired(m => m.LivingCity)
               .WithMany(m => m.LivingCityStudents).HasForeignKey(m=>m.LivingCityId);
}

Yul

Thanks a lot for your answer. As you and @ChrisPratt said, there are two ways to achieve this and both of them is working. As Data Annotations way seems to be much more easier to control entities for this operation, I would prefer to use that way. Many thanks again for your consideration. Regards... – Cicatrix

What if the relationship between Student and City was 1 -> 0..1 ? How could you implement that with the fluent API – Benevento

@octavioccl this answer is exactly how this should be handled. This creates proper navigation along with the proper set of ForeignKeys in the DB. I find the InverseProperty and ForeignKey attributes the easiest to read the code and actually tell what it is doing. – Sextain

I had to make BirthCityID and HomeCityID nullable (int?) in order for the Data Annotation-method to work. Else I always got the error SqlException: Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. – Croydon

what is doing by Students property in City class? – Smirk

It is important to define the two foreign keys. Defining only the one that have different names doesn’t work. – Bowser

Answer 2

Sheesh. It's been a long day. There's actually a very big, glaring problem with your code, actually, that I completely missed when I commented.

The problem is that you're using a single collection of students on City. What's actually happening here is that EF can't decide which foreign key it should actually map that collection to, so it creates another foreign key specifically to track that relationship. Then, in effect you have no navigation properties for the collections of students derived from BirthCity and LivingCity.

For this, you have to drop down to fluent configuration, as there's no way to configure this properly using just data annotations. You'll also need an additional collection of students so you can track both relationships:

public class City
{
    ...

    public virtual ICollection<Student> BirthCityStudents { get; set; }
    public virtual ICollection<Student> LivingCityStudents { get; set; }
}

Then, for Student:

public class Student
{
    ...

    public class StudentMapping : EntityTypeConfiguration<Student>
    {
        public StudentMapping()
        {
            HasRequired(m => m.BirthCity).WithMany(m => m.BirthCityStudents);
            HasRequired(m => m.LivingCity).WithMany(m => m.LivingCityStudents);
        }
    }
}

And finally in your context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new Student.StudentMapping());
}

Philps

Thanks a lot for your answer. As you and @octavioccl said, there are two ways to achieve this and both of them is working. As Data Annotations way seems to be much more easier to control entities for this operation, I would prefer to use that way. Many thanks again for your consideration. Regards... – Cicatrix

Thanks for the StudentMapping class... I've been wrestling with where these definitions should live and I really like that they are visible from the Student class - where the information is relevant - not in the context. – Strum

@erroric: Yep, I can't remember where I stumbled upon this originally, but it's miles better than sticking it on the context as every single tutorial everywhere shows you. Before discovering this approach, my single biggest problem with fluent config was that it wasn't with the entity, so unless you were mindful of which entities were configured via the context, it lead to a lot of confusion. – Philps

Thanks, Just helped me outta hole! – Chelton