One to zero-or-one with HasForeignKey

Question

I have two models:

public class Person
{
    public virtual int Id { get; set; }
    public virtual Employee Employee { get; set; } // optional
}

public class Employee
{
    public virtual int Id { get; set; }
    public virtual int PersonId { get; set; }

    public virtual Person Person {get; set; } // required
}

public class EmployeeConfiguration : EntityTypeConfiguration<Employee>
{
    public EmployeeConfiguration()
    {
        Property(e=>e.PersonId) // I need this property mapped
            .HasColumnName("person_id")
            .HasColumnType("int");
    }
}

I want to map them using fluent mapping. Employee table has column 'person_id' which is non-nullable. I tried following:

HasRequired(e => e.Person)
    .WithOptional(p => p.Employee)
    .Map(m => m.MapKey("person_id"));

But it fails with:

System.Data.Entity.ModelConfiguration.ModelValidationException : One or more validation errors were detected during model generation:

person_id: Name: Each property name in a type must be unique. Property name 'person_id' is already defined.

I need PersonId property on its own, so what I basically want is:

HasRequired(e => e.Person)
    .WithOptional(p => p.Employee)
    .HasForeignKey(e => e.PersonId); // there is no such method

But there is no such method here as HasForeignKey

There is no HasForeignKey method, because for this kind of `HasRequired().WithOptional()` relationship, the optional side (`Employee`) will use its primary key as the foreign key. — jjj, May 19 '15 at 23:27
Using your case as an example, this makes sense because there are no `Employee` entities that don't have a corresponding `Person`, so having both `Employee.PersonId` and `Employee.Id` is redundant. If you want `PersonId` to be the foreign key, you can use `HasKey(emp => emp.PersonId)`. — jjj, May 19 '15 at 23:27
Once you have multiples of this type of relationship, things fall apart, though. — jjj, May 19 '15 at 23:28

Sergey Berezovskiy · Accepted Answer · 2013-09-10T13:59:56.823

55

OK, I figured that out - you should use WithMany (yep, not obvious) in order to store foreign key in some property:

Property(e => e.PersonId).HasColumnName("person_id");
HasRequired(e => e.Person)
    .WithMany()
    .HasForeignKey(p => p.PersonId);

See One-to-One Foreign Key Associations article for details. BTW this will create employee foreign key column for person's table, but there is no other way to have navigation property and foreign key separately.

If you need foreign key read-only, you can change PersonId property to:

public int PersonId { get { return Person.Id; } }

And use your original mapping

HasRequired(e => e.Person)
    .WithOptional(p => p.Employee)
    .Map(m => m.MapKey("person_id"));

edited Sep 10 '13 at 13:59

answered Sep 10 '13 at 13:14

Sergey Berezovskiy

232,247
41
429
459

From the article: "As you may have noticed, both associations in the fluent API code has been configured as a many-to-one—not one-to-one, as you might have expected. The reason is simple: Code First (and EF in general) does not natively support one-to-one foreign key associations." – SWeko Sep 10 '13 at 13:47
1

Ad 1: problem is, I need Person.Employee navigation Property which this solution does not provide Ad 2: nice hack, but it forces join in case I need PersonId only (without whole Person) Ad article: I'll look into it later – joozek Sep 10 '13 at 14:08
35

This is a dumb way to have to configure this relationship as defeats the point of `.HasRequired().WithOptional()` fluent syntax, EF should just add `.HasForeignKey()` to the above fluent chain or not provide the above chain at all. – undefined Mar 09 '14 at 02:21
1

This has been driving my up the wall. Thank you for pointing this out! – Luke Dec 02 '14 at 11:56
@joozek: Have you find a solution to this problem without forcing the join? – Tom Pažourek Aug 31 '15 at 14:04
@TomPažourek whoa, dude, It's been some time! If I recall correctly I didn't find satisfying solution and ended up avoiding the need for such mapping – joozek Aug 31 '15 at 14:35
@joozek: Thanks for answer. I couldn't find a solution anywhere, only people having the same issue. I tried to isolate the problem and posted new question: http://stackoverflow.com/questions/32313842/mapping-foreign-key-in-hasoptional-withoptionaldependent-relation-in-entity Hopefully I'll find the answer. – Tom Pažourek Aug 31 '15 at 14:42
The design of this EF fluent API is so bad, it would take an incredible amount of effort to make it any more counter-intuitive. – Shawn J. Molloy Sep 01 '23 at 00:58

score 5 · Answer 2 · answered Mar 08 '17 at 19:37

5

There's actually a better way to do this:

HasKey(e => e.PersonId);

HasRequired(e => e.Person)
    .WithOptional(p => p.Employee);

Note that you no longer need the Employee.Id property with this approach, as it was superfluous in terms of a 1 to 0 or 1 relationship in the first place.

answered Mar 08 '17 at 19:37

joelmdev

11,083
10
65
89

Thanks for your answer! I don't remember clearly circumstances of my question (it was a few years ago), but I'm certain that I couldn't remove `Employee.Id`, because I was working with legacy db that I didn't own – joozek Mar 09 '17 at 09:06

One to zero-or-one with HasForeignKey

2 Answers2

Linked