One to zero/one relationship (Code First)

Question

I'm trying this class:

public class Person
{
    public int PersonID { get; set; }
    public string Name { get; set; }
    public int SpouseID { get; set; }
    public virtual Person Spouse { get; set; }
}

The relationship is: One Person has one or zero Spouse / One Spouse has a required Person

Is it possible to build this model in Fluent API?

Thanks.

score 6 · Accepted Answer · edited May 23 '17 at 12:26

Unfortunately it is not possible to make real self referencing one-to-one relation because at the moment EF doesn't support unique keys. To make this real self referencing one-to-one SpouseID must be marked as unique. EF allows one-to-one only on primary keys (primary key in dependent entity must be also foreign key to principal entity) which means that self referencing one-to-one relation would end in PersonID <-> PersonID = two entities with same PersonID in single table. It is not possible because PersonID is primary key and it must be unique.

You can handle it as one-to-many and expose only single navigation property as you do know:

modelBuilder.Entity<Person>()
            .HasOptional(p => p.Spouse)
            .WithMany()
            .HasForeingKey(s => s.SpouseID);

To enforce one-to-one relation in database you will add custom database initializer and manually create unique index on SpouseID column.

The problem is that it is only one way navigation so you can get Person's spouse but from that Spouse you cannot get back to husband.

score 2 · Answer 2 · answered Aug 19 '13 at 12:13

This answer is suggestion instead of an exact answer.

In real world scenario this isn't correct design, first of all every spouse has spouse as well that references same person, for example, Spouse of Spouse of Person is Person itself. We have modeled this in our app as Persons and PeronRelations, PersonRelations have FromPersonID, ToPersonID and Type property. Type specifies "Husband<->Wife", "Wife<->Husband", "Father<->Son", "Son<->Father" etc.

public class Person{
   public int PersonID {get;set;}
   ...
   public ICollection<PersonRelations> FromRelations {get;set;}
   public ICollection<PersonRelations> ToRelations {get;set;}

}

public class PersonRelations{

   .. 
   public int FromPersonID {get;set;}
   public int ToPersonID {get;set;}

   public RelationType Type {get;set;} 
   public Person FromPerson {get;set;}
   public Person ToPersion {get;set;}

   // useful for Employment and Marriage 
   // durations
   public DateTime? Start {get;set;}
   public DateTime? End {get;set;}
}

public enum RelationType{
   Husband_Wife,
   Wife_Husband,
   Father_Son,
   Son_Father,
   Friend_Friend,
   Employee_Employer,
   Employer_Employee
}

This gives you advantage as you can store more information per relation and navigate them in both directions along with many queries.

Every relation has corresponding opposite relation as well. And you will have to write extra logic to maintain proper inverse relation as well in order for it to work correctly.

And this is very easy to implement in Entity Framework as it is simple One to Many relation.

One to zero/one relationship (Code First)

2 Answers2