c# - Entity Framework Core zero-or-one to zero-or-one relation

Question

Given these classes:

public class A
{
    public int Id {get; set;}
    public int? BId {get; set;}
    public B B {get; set;}
}

public class B
{
    public int Id {get; set;}
    public int? AId {get; set;}
    public A A {get; set;} 
}

Then with Fluent API

 modelBuilder.Entity<A>()
                .HasOne(a => a.B)
                .WithOne(b => b.A)
                .HasForeignKey<A>(a => a.BId);

When creating objects and add them to database, things look like following in the corresponding tables:

• [A].BId is set
• [B].AId = null

When I retrieve data using EF Core:

• A.B is set, A.BId is set
• B.A is set, but B.AId is null.

What should I do to have B.AId set as well?

Answer 1

These 0..1 : 0..1 relations are usually defined between entities of which none is an obvious principal entity. I like the example of cars and drivers, which is a bit more imaginable than A and B.

The model you're after looks like this:

There are two mutual foreign keys, both of which have a unique index to enforce 1:1 at the database level.

The HasOne - WithOne combi can't be used here, because that always requires a HasForeignKey instruction to tell which entity is principal. This also configures only one field as foreign key. In your example, B.AId is just a regular field. If you don't give it a value, EF won't either.

The mapping of the above model is a bit more cumbersome than HasOne - WithOne:

var carEtb = modelBuilder.Entity<Car>();
var driverEtb = modelBuilder.Entity<Driver>();

carEtb.HasOne(c => c.Driver).WithMany();
carEtb.HasIndex(c => c.DriverID).IsUnique();

driverEtb.HasOne(d => d.Car).WithMany();
driverEtb.HasIndex(c => c.CarID).IsUnique();

So there are two 0..1:n association that are made unique by indexes on the foreign keys.

Which creates the following database model:

  CREATE TABLE [Drivers] (
      [ID] int NOT NULL IDENTITY,
      [Name] nvarchar(max) NULL,
      [CarID] int NULL,
      CONSTRAINT [PK_Drivers] PRIMARY KEY ([ID])
  );

  CREATE TABLE [Cars] (
      [ID] int NOT NULL IDENTITY,
      [Brand] nvarchar(max) NULL,
      [Type] nvarchar(max) NULL,
      [DriverID] int NULL,
      CONSTRAINT [PK_Cars] PRIMARY KEY ([ID]),
      CONSTRAINT [FK_Cars_Drivers_DriverID] FOREIGN KEY ([DriverID])
          REFERENCES [Drivers] ([ID]) ON DELETE NO ACTION
  );

  CREATE UNIQUE INDEX [IX_Cars_DriverID] ON [Cars] ([DriverID])
      WHERE [DriverID] IS NOT NULL;


  CREATE UNIQUE INDEX [IX_Drivers_CarID] ON [Drivers] ([CarID])
      WHERE [CarID] IS NOT NULL;

  ALTER TABLE [Drivers] ADD CONSTRAINT [FK_Drivers_Cars_CarID] FOREIGN KEY ([CarID])
      REFERENCES [Cars] ([ID]) ON DELETE NO ACTION;

It creates two nullable foreign keys both indexed by a unique filtered index. Perfect!

But...

EF doesn't see this as a bidirectional one-on-one relationship. And rightly so. The two FKs are just that, two independent FKs. However, in view of data integrity the relationship should be established by both ends: if a driver claims a car (sets driver.CarID), the car should also be attached to the driver (set car.DriverID), otherwise another driver could be connected to it.

When existing car and drivers are coupled a little helper method could be used, for example in Car:

public void SetDriver(Driver driver)
{
    Driver = driver;
    driver.Car = this;
}

However, when both a Car and Driver are created and associated in one process, this is clumsy. EF will throw an InvalidOperationException:

Unable to save changes because a circular dependency was detected in the data to be saved: 'Car [Added] <- Car { 'CarID' } Driver [Added] <- Driver { 'DriverID' } Car [Added]'.

Which means: one of the FKs can be be set at once, but the other one can only be set after saving the data. That requires two SaveChanges calls enclosed by a transaction in a pretty imperative piece of code:

using (var db = new MyContext())
{
    using (var t = db.Database.BeginTransaction())
    {
        var jag = new Car { Brand = "Jaguar", Type = "E" };
        var peter = new Driver { Name = "Peter Sellers", Car = jag };

        db.Drivers.Add(peter);

        db.SaveChanges();

        jag.Driver = peter;

        db.SaveChanges();
        t.Commit();
    }
}

Alternative: junction table

So now the reason why I go to these lengths explaining all this: in my opinion, 0..1 : 0..1 associations should be modeled by a junction table with unique foreign keys:

By using a junction table -

1. The association can be established in an atomic operation instead of an error-prone operation of setting two foreign keys.
2. The entities themselves are independent: they don't have foreign keys they don't really need to fulfill their role.

This model can be implemented by this class model:

public class Car
{
    public int ID { get; set; }
    public string Brand { get; set; }
    public string Type { get; set; }
    public CarDriver CarDriver { get; set; }
}

public class Driver
{
    public Driver()
    { }
    public int ID { get; set; }
    public string Name { get; set; }
    public CarDriver CarDriver { get; set; }
}

public class CarDriver
{
    public int CarID { get; set; }
    public Car Car { get; set; }
    public int DriverID { get; set; }
    public virtual Driver Driver { get; set; }
}

And the mapping:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var carDriverEtb = modelBuilder.Entity<CarDriver>();
    carDriverEtb.HasKey(cd => new { cd.CarID, cd.DriverID });
    carDriverEtb.HasIndex(cd => cd.CarID).IsUnique();
    carDriverEtb.HasIndex(cd => cd.DriverID).IsUnique();
}

Now creating drivers and cars and their associations can easily be done in one SaveChanges call:

using (var db = new MyContext(connectionString))
{
    var ford = new Car { Brand = "Ford", Type = "Mustang" };
    var jag = new Car { Brand = "Jaguar", Type = "E" };

    var kelly = new Driver { Name = "Kelly Clarkson" };
    var peter = new Driver { Name = "Peter Sellers" };

    db.CarDrivers.Add(new CarDriver { Car = ford, Driver = kelly });
    db.CarDrivers.Add(new CarDriver { Car = jag, Driver = peter });

    db.SaveChanges();
}

The only drawback is that navigting from Car to Driver vv is a bit les convenient. Well, see for yourself which model suit you best.