c# - One to One Relationship with Different Primary Key in EF 6.1 Code First

Question

I am having an issue getting a reference to the employee object from the PayGroup object using Entity Framework 6.1. I have a foreign key in the database on PayGroup.SupervisorId -> Employee.EmployeeId. Note that this is a zero or one-to-one relationship (a pay group can only have one supervisor and an employee can only be the supervisor of one pay group).

According to this post on GitHub, it is not possible to have a foreign key on a table with a different primary key. I've added the foreign key to the database manually but I can't figure out how to set up the fluent api mapping to be able to get the employee object from pay group.

Pay Group Table

Employee Table

Note: There is a foreign key from PayGroup.SupervisorId - Employee.EmployeeId in the database.

Below are the DTO's (I don't currently have any working relationship mapping between these classes):

public class PayGroup
{
    public int Id { get; set; }
    public string SupervisorId { get; set; }
    public virtual Employee Supervisor { get; set; }
}

public class Employee
{
    public string EmployeeId { get; set; }
    public string FullName { get; set; }
}

Answer 1

one-to-one relationship with explicit FK property (like your PayGroup.SupervisorId) is not supported.

So remove that property from the model:

public class PayGroup
{
    public int Id { get; set; }
    public virtual Employee Supervisor { get; set; }
}

and use the following fluent mapping:

modelBuilder.Entity<PayGroup>()
    .HasRequired(e => e.Supervisor)
    .WithOptional()
    .Map(m => m.MapKey("SupervisorId"));

The WithOptional() call specifies two things. First that there is no inverse navigation property in Employee class, and second that the FK is optional (Allow Nulls = true in the table).

If you decide to add inverse navigation property

public class Employee
{
    public string EmployeeId { get; set; }
    public string FullName { get; set; }
    public virtual PayGroup PayGroup { get; set; } // <=
}

change it to WithOptional(e => e.PayGroup).

If you want to make it required (Allow Nulls = false in the table), then use the corresponding WithRequiredDependent overload (Dependent here means that the Employee will be the principal and PayGroup will be the dependent).