Entity Framework foreign key references not mapping

Question

Using EF6 and .NET 4.6.1. I know this somewhat is a duplicate (I've looked at this stack overflow post) but hear me out. My case is different and I have tried to get it to work using their solutions with mine but it didn't work. So no this isn't actually a duplicate. It's another issue altogether and I haven't found a post that really helps me on this topic.

I am trying to map relationships between 3 models; Employee, Position and Employment. I want a one-to-many between Employment and Position (employments map to one position) and a one-to-one between Employment and Employee.

public class Employment
{
    public int EmploymentID { get; set;}
    ...
    public Position Position { get; set; }
    public Employee Employee { get; set; }
}

public class Position
{
    public int PositionID { get; set;}
    ...
    [InverseProperty("Position")]
    public ICollection<Employment> Employments { get; set; }
}

public class Employee
{
    public int EmployeeID { get; set;}
    ...
    [InverseProperty("Employee")]
    public Employment Employment { get; set; }
}

However, when I try to run this with DbContext automapping, it fails and says it can't find the relationships. I've tried multiple combinations of data annotations like setting inverseproperties and foreignkey("____ID") on some of them but haven't been able to get it to work.

I did also try adding virtual keywords in for some of the but that didn't do anything either.

I'd rather not use FluentAPI as I want to let the auto mapper do as much as possible with this. It's not complicated a problem enough to warrant manually mapping it with FluentAPI (At least in my opinion it isn't. Maybe I'm wrong).

What data annotations do I need? I've looked at this stack overflow post and various articles on entityframeworktutorial.net trying to apply their solutions to my case. But haven't gotten anything to work.

The failing line is here:

using (EmploymentContext ctx = new EmploymentContext())
{
    Position pos = new Position()
    {
        PositionID=1,
        Name="General Manager"
    };
    ctx.Positions.Add(pos); // Failing here
    ctx.SaveChanges();
}

and the error message is:

'Unable to determine the principal end of an association between the types 'Ianmann.Hr.DataAccess.Employment.Employee and Ianmann.Hr.DataAccess.Employment.Employment. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.'

Answer 1

I don't know why you insist on using data annotations. Everything you can do with data annotation can be done with the Fluent API, but the opposite is not true. Also, and especially with relationships, the data annotations are not intuitive and error prone.

In the concrete case the problem is with the one-to-one relationship (btw, the error message should contain that information). It's because EF cannot determine the principal and the dependent of the relationship when the ends of the relationship are both optional (as in your case) or both required. So one way to resolve it is to mark the principal by making the navigation property required:

public class Employment
{
    public int EmploymentID { get; set; }
    ...
    public Position Position { get; set; }

    [Required] // <--
    public Employee Employee { get; set; }
}

The InverseProperty is redundant (not needed) in this case.

The same can be achieved more intuitively with fluent API:

modelBuilder.Entity<Employment>()
    .HasRequired(e => e.Employee)
    .WithOptional(e => e.Employment);

But please note that while either way will resolve the issue in question, the resulting design will be the so called Shared Primary Key association, where EmploymentID is both PK and also FK to Employee. In case you want a separate FK property / column, then fluent API is a must as it cannot be done via data annotations:

modelBuilder.Entity<Employment>()
    .HasRequired(e => e.Employee)
    .WithOptional(e => e.Employment)
    .Map(m => m.MapKey("EmployeeID"));