EF6 Foreign key with one-to-one relationship and t

Question

I cannot seem to get EF6 to generate a FK when I am using two-way navigation.

Here are my classes (removed some properties for brevity):

public class BaseSystem
{
    public int Id { get; set; }
    public SystemConfiguration Configuration { get; set; }
}

public class SystemConfiguration
{
    public int Id { get; set; }
    public BaseSystem System { get; set; }
}

I want the SystemConfiguration table to have a FK reference back to BaseSystem.Id.

Here's my fluent API code:

modelBuilder.Entity<SystemConfiguration>()
    .HasRequired(x => x.System)
    .WithOptional(x => x.Configuration);

When I use two-way navigation, this migration is generated which seems to generate the FK at first:

CreateTable(
    "MYDB.SYSTEMCONFIGURATION",
    c => new
        {
            ID = c.Decimal(nullable: false, precision: 10, scale: 0)
        })
    .PrimaryKey(t => t.ID)
    .ForeignKey("MYDB.BASESYSTEM", t => t.ID);

After noticing that the SQL it generates doesn't include the FK, I removed the BaseSystem.Configuration property and the migration generates this:

CreateTable(
    "MYDB.SYSTEMCONFIGURATION",
    c => new
        {
            ID = c.Decimal(nullable: false, precision: 10, scale: 0, identity: true),
            SYSTEM_ID = c.Decimal(nullable: false, precision: 10, scale: 0),
        })
    .PrimaryKey(t => t.ID)
    .ForeignKey("CISRF.BASESYSTEM", t => t.SYSTEM_ID, cascadeDelete: true);

My SQL also generates "SYSTEM_ID" number(10, 0) not null as expected which it does not do when I have two-way nav.

What do I need to do to generate the FK on the SystemConfiguration table while also still having two-way navigation?

Answer 1

When working with one-to-one relationship, EF6 uses the Id column in the child as the FK to the parent, it won't create a second column for FK because that would be considered one-to-many relationship, because that second column would allow a parent Id to exist in many child entries, whereas the PK as FK wouldn't.

When you removed the Configuration property from your parent class, EF6 understood your child class had a required System as parent, but there was nothing saying it should be one-to-one, so EF6 would treat as a one-to-many relationship by default, since it created that second column, which would allow the same parent in many child entries.

Some may argue you could create a unique index for that second column as FK to avoid having the same parent id, but EF6 doesn't work this way (maybe they think it doesn't make sense doing this).

The first option you tried is the right way to configure a one-to-one relationship, and the Id in the SystemConfiguration is a PK and a FK to BaseSystem at the same time.