A dependent property in a ReferentialConstraint is mapped to a store-generated column error on 1-to-1 relationship

ef-code-first, entity-framework

Solution

Here there are two problems, the obvious one, shown in the exception. When you define a one-to-one relationship, the FK must be also the PK. In this case the PK and FK of both entities is the `Id` field. The problem shown in the exception is that the FK is database generated. So, if you insert a `Child1` with a related `Child2`, EF has no way to set the FK value of the related `Child2` because it's database generated.

The second problem, that has still not arisen, is that a one-to-one relationship is only a theoric thing in a database like SQL Server. If you want to insert `Child1` that depends on `Child2`, you need to insert first `Child1`, and then the related `Child2`. That's right, but, ooops, you also have to insert `Child2` before inserting `Child1`, because `Child1` depends also on `Child2`. So, having a pure one to one relationship is not possible.

To solve this problem you need to do two things:

- make the relationship a 1-to-(0 or 1). I.e. you must have a principal entity and a dependent entity which can or cannot exist. This will allow you to insert the principal entity, without the dependent entity, because with this configuration you can isnert the principal without the dependent.

- the principal PK can be left as database generated, but you have to change the PK on the dependent entity not to be db generated. So, when you insert the dependent entity the PK, which is also the FK, can be freely specified by EF.

Finally, if you think of it, a 1-to-1 relationship usually makes no sense. You can use a single table that holds all the columns in both tables, because whenver a row exists in table A, it must exists in table B and viceversa. So having a single table has the same effect.

However, if you still want to use the 1-to-1 relationship, EF allows you to model it like this:

modelBuilder.Entity<Child1>()
            .HasRequired(c1 => c1.Child2)
            .WithRequiredPrincipal(c2 => c2.Child1);

Note that, in this case, the EF abstraction takes care to allow you to have a 1-to-1 relationship, even if it cannot exists in the DB. However, it's necessary to specify this relationship using the `ModelBuilder` because you need to specify a principal and a dependent side. In this case the principal is `Child1` and the dependent is `Child2`. Note that you still have to be careful with the rule for database generated values.

NOTE that this is modelled in the DB with a single FK from `Child2` to `Child1`, and not FK from `Child1` to `Child2`. So, in the DB is a (1)-to(0 or 1) relationship, as explained above

public class Child1
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  // Leave as is
    public int Id { get; set; }
    ...


public class Child2
{
    //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]  // Not db-generated
    //[ForeignKey("Child1")] -- specified in the model builder
    public int Id { get; set; }

Problem

Original source