4

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

Pay Group Table

Employee 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; }
}
Brian Swart
  • 922
  • 1
  • 9
  • 25
  • You try something like [this](http://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx)? – Steve Greene Jun 14 '17 at 17:25

1 Answers1

13

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).

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • 1
    So simple when it's explained clearly. I think I was getting messed up by the WithOptional and HasRequired. I also was doing my best to not remove the SupervisorId field from PayGroup. Thanks! – Brian Swart Jun 14 '17 at 18:21
  • well, this works to some degree. if in the **Pay Group Table** the "SupervisorId" is NULL in the db, then when I do a query with link, I get an error saying that the null value can not be converted to a non-nullable type. – Alin I Mar 23 '20 at 13:28