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

Question

In EntityFramework code first models, there exists a 1:1 relationship:

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

    public string Name { get; set; }

    public Child2 Child2 { get; set; }
}

public class Child2
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [ForeignKey("Child1")]
    public int Id { get; set; }

    public string Name { get; set; }

    public Child1 Child1 { get; set; }
}

When I tried to insert some data to the database, it thrown an exception:

{"A dependent property in a ReferentialConstraint is mapped to a 
  store-generated column. Column: 'Id'."}

It seems I cannot use auto generated Id for Child2, how can I keep this feature and make the relationship established successfully meanwhile?

Answer 1

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:

1. 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.
2. 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; }