Optional one-to-one relationship in entitiy framework does not result in the right Foreign-Primary key in my sql database

Question

My use case is pretty much identical to what I see in the tutorials: I have a class Slachtoffer and another class Schadeberekening. Each Schadeberekening must have a Slachtoffer, and a Slachtoffer can have a Schadeberekening.

I am trying to set up the database using code-first like this:

public class Slachtoffer
{
    public int Id { get; set; }

    public int? SchadeberekeningId{ get; set; }
    public Schadeberekening? Schadeberekening { get; set; }
}

public class Schadeberekening
{
    public int Id { get; set; }

    public int SlachtofferId { get; set; }
    public Slachtoffer Slachtoffer { get; set; }
}

modelBuilder.Entity<Slachtoffer>()
             .HasOne(s => s.Schadeberekening)
             .WithOne(ad => ad.Slachtoffer)
             .HasForeignKey<Schadeberekening>(ad => ad.SlachtofferId);

modelBuilder.Entity<Schadeberekening>()
             .HasOne<Slachtoffer>(a => a.Slachtoffer)
             .WithOne(sa => sa.Schadeberekening)
             .HasForeignKey<Slachtoffer>(sa => sa.SchadeberekeningId);

Yet when I look at the created tables, only in the Slachtoffer table, the PK from the Schadeberekening is considered as foreign key. In the Schadeberekening table, it is just an "int".

How can I fix this?

Answer 1

In the Schadeberekening, it is just an "int". How can I fix this?

I have a suggestion like below, remove the code in OnModelCreating, and modify your code like:

Slachtoffer:

public class Slachtoffer
    {
        public int Id { get; set; }

        [ForeignKey("Schadeberekening")]
        public int? SchadeberekeningId { get; set; }
        public virtual Schadeberekening? Schadeberekening { get; set; }
    }

Schadeberekening:

public class Schadeberekening
    {
        public int Id { get; set; }

        [ForeignKey("Slachtoffer")]
        public int SlachtofferId { get; set; }

        public virtual Slachtoffer Slachtoffer { get; set; }
    }

result:

Answer 2

One to one relationships in EF can be configured in two ways. The default, and best enforceable way is to join the two tables on their PKs. You would choose one entity as the Root for the relationship, and the related table would share that Id serving as both the PK and the FK back to the root. The relationship can still be nullable (1-to-0-or-1):

public class Slachtoffer
{
    public int Id { get; set; }

    public Schadeberekening? Schadeberekening { get; set; }
}

public class Schadeberekening
{
    [Key, ForeignHey(nameof(Slachtoffer))]
    public int Id { get; set; }

    public virtual Slachtoffer Slachtoffer { get; set; }
}

Note that there are no FK fields in either table, the Schadeberekening associated to a Slachtoffer will share the same ID. If you are explicitly managing the key setup, the Id in the Slachtoffer would be treated as an Identity, while in the Schadeberekening it should not be, as Slachtoffer will be responsible for controlling that ID.

The second way to configure a 1-to-1 is using an explicit FK. This would only exist on one table or the other, not both. So for example:

public class Slachtoffer
{
    public int Id { get; set; }

    public int? Schadeberekening { get; set; }
    public Schadeberekening? Schadeberekening { get; set; }
}

public class Schadeberekening
{
    public int Id { get; set; }

    public virtual Slachtoffer Slachtoffer { get; set; }
}

In this case you would need to tell EF about the 1-to-1 relationship and that it should locate the FK in the Slachtoffer table using the prescribed FK column. (This ideally should be set up as a Shadow Property in the entity)

If configured as a 1-to-1 relationship, EF will do its best to respect that constraint, but at the database level this is a many-to-one relationship unless you add a unique constraint on Slacktoffer.SchadeberekeningId. (Code-first may do this as part of the HasOne().WithOne()) You don't have another FK in the secondary table to go back to the parent since that creates the potential to have broken and invalid relationships. For instance if I have A referencing B with a reference back to A, If I change A's reference to C, it is possible that B's reference back to A does not get removed, or C to A does not get created.

A good breakdown of options for creating one-to-one relationships can be found here: https://learn.microsoft.com/en-us/ef/core/modeling/relationships/one-to-one