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; }
}

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);
}