How to create optional constraint in a one to one relationship using Entity Framework Code First

Question

How can I set the ForeignKeys (ideally using Data Annotations) in this scenario:

[Table("Teacher", Schema = "Account")]
public partial class Teacher
{
    [Key]
    public int teacherId { get; set; }

    public string name { get; set; }

    public virtual Address Address { get; set; }
}

[Table("Student", Schema = "Account")]
public partial class Student
{
    [Key]
    public int studentId { get; set; }

    public string name { get; set; }

    public virtual Address Address { get; set; }
}

Both tables Student and Teacher will have one address.

[Table("Address", Schema = "Location")]
public partial class Address
{
    [Key]
    public int addressId { get; set; }

    public string details { get; set; }

    public virtual Student Student { get; set; }

    public virtual Teacher Teacher { get; set; }
}

Table address should have a constraint to either Teacher OR Student tables, meaning that each row in Address table must be linked to either a Student OR a Teacher.

I just cannot find a way to achieve this. With the current code, when adding migration, I got the error: Unable to determine the principal end of an association between the types 'Student' and 'Address'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

You may need to deal with that on the database side: http://stackoverflow.com/questions/26224548/one-out-of-2-properties-should-be-null-entityframework-code-first — Steve Greene, Jan 25 '16 at 15:19
Hi Steve, yes I had seen that article. It is good to know that I have the alternative to achieve this directly in the database, however I am curious to know how to achieve this using Code First or at least to understand that that is a limitation of EF Code First — WPalombini, Jan 25 '16 at 22:45

score 0 · Answer 1 · answered Feb 03 '16 at 12:53

You can achieve this two ways. 1. There is no need to add navigation property on Address class.

[Table("Teacher", Schema = "Account")]
public partial class Teacher
{
    [Key]
    public int teacherId { get; set; }

    public string name { get; set; }

    public virtual Address Address { get; set; }
}

[Table("Student", Schema = "Account")]
public partial class Student
{
    [Key]
    public int studentId { get; set; }

    public string name { get; set; }

    public virtual Address Address { get; set; }
}

[Table("Address", Schema = "Location")]
public partial class Address
{
    [Key]
    public int addressId { get; set; }

    public string details { get; set; }

}

There will be created two relation table Student - Address and Teacher - Address. 2. You can use nullable foreign key property. Like this;

[Table("Address", Schema = "Location")]    
public partial class Address   {

[Key]
public int addressId { get; set; }    
public string details { get; set; }    
public int? teacherId { get; set; }
public int? studentId { get; set; }

[ForeignKey("studentId")]
public virtual Student Student { get; set; }
[ForeignKey("teacherId")]
public virtual Teacher Teacher { get; set; }
}

By the way, if you are interesting to achieve kind of way like this, you may look Inheritance Strategy in Code-First. By using inheritance strategy, you can create a main class and inherit Student and Teacher class from it. After that, use main class id as FK on Address.

How to create optional constraint in a one to one relationship using Entity Framework Code First

1 Answers1