CODEWITHSUNDEEP
asp.net-mvcentity-frameworkef-code-first
Mahmud Sabuj
Mahmud Sabuj

Reputation: 9

Entity Framework - same foreign key in multiple field

I have 2 tables:

Cities

cityId
CityName

Employees

EmpId
Name
BirthCity - foreign key - cityid
LivingCity - foreign key - cityid

How can I use the same CityId twice in the Employee table as foreign key using Entity Framework with a code-first approach?

Upvotes: 0

Views: 1081

Answers (1)

Steve Py
Steve Py

Reputation: 34653

What you are looking for is two instances of a many-to-one. The Foreign Key is located on the Employees table.

For example: (Table)

EmpId
Name
BirthCityId
LivingCityId

You can declare the FK properties and wire them up via attributes or configuration, or configure them via shadow properties (recommended)

Wired up with attributes:

[Table("Employees")]
public class Employee
{
    [Key]
    public int EmpId { get; set; }
    public string Name { 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; }
}

Using shadow properties via configuration (Either modelBuilder {below} or EntityTypeConfiguration)

EF Core:

[Table("Employees")]
public class Employee
{
    [Key]
    public int EmpId { get; set; }
    public string Name { get; set; }

    // No FK declarations.

    public virtual City BirthCity{ get; set; }
    public virtual City LivingCity{ get; set; }
}


modelBuilder.Entity<Employee>(e =>
{ 
    e.HasKey(x => x.EmpId);
    e.Property(x => x.EmpId).HasDatabaseGenerated(DatabaseGenerated.Identity); // For DB managed identity column..
    e.HasOne(x => x.BirthCity)
        .WithMany()
        .HasForeignKey("BirthCityId");
    e.HasOne(x => x.LivingCity)
        .WithMany()
        .HasForeignKey("LivingCityId");
});

EF6 can manage Shadow Properties as well via .Map(x => x.MapKey()

When it comes to code-first: If you declare your entity without wiring a FK up, EF will assign a shadow property for both of the cities based on the navigation property type. The table would most likely end up with something like City_Id and City1_Id for the BirthCity and LivingCity respectively. (EF convention uses the "type" rather than the property name)

I recommend using shadow properties for FKs to avoid situations where there are two sources of truth for a relationship within the entity. (Some code may rely on employee.BirthCityId while other code uses employee.BirthCity.CityId. Changing a FK property value can have different effects on an entity depending on whether the related entity is eager loaded or not so it is generally better to just use the navigation property and "hide" the FK.

Upvotes: 2

Related Questions