Is it possible to capture a 0..1 to 0..1 relationship in Entity Framework?

Question

Is there a way to make a nullable reverse navigation property for a nullable Foreign Key relationship in Entity Framework? In database parlance, a 0..1 to 0..1 relationship.

I've tried as below, but I keep getting the error message:

Unable to determine the principal end of an association between the types 'Type1' and 'Type2'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

public class Type1 {

    public int ID { get; set; }

    public int? Type2ID { get; set; }
    public Type2 Type2 { get; set; }
}

public class Type2 {

    public int ID { get; set; }

    public int? Type1ID { get; set; }
    public Type1 Type1 { get; set; }
}

I understand that the integer column can only exist in one table or the other, but surely it should be possible to cover all necessary cases? E.g:

Type1      Type2
========   ===============
ID         ID   | Type1ID
--------   ---------------
1          1    | null
2          2    | 2

I've tried using data annotations (e.g. [ForeignKey] on one end, [InverseProperty] on both), but none of those seem to help the matter.

If possible, a data annotation solution would be preferred over Fluent API. Also, the int? property isn't strictly necessary from a domain perspective for either class, if that helps.

There is an interesting work-around here which implies it isn't possible to capture this kind of relationship in Entity Framework (effectively, an item that is optionally part of a collection) - if so, is there any documentation that would support this?.

Answer 1

In EF6 and earlier it wasn't all that easy to implement such an association correctly. Fortunately, EF-core has greatly improved in supported associations. Now it's a piece of cake to implement the only model that enforces this kind of association by database constraints. That is: a junction class between Car and Driver in which the foreign keys have unique indexes (option 4 below). And it even almost entirely works with default mapping conventions.

The model:

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

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

class CarDriver
{
    public int CarId { get; set; }
    public int DriverId { get; set; }
    public Car Car { get; set; }
    public Driver Driver { get; set; }
}

The only required explicit mapping:

class CarDriverConfig : IEntityTypeConfiguration<CarDriver>
{
    public void Configure(EntityTypeBuilder<CarDriver> builder)
    {
        builder.HasKey(cd => new { cd.CarId, cd.DriverId });
    }
}

That's all EF needs to create the correct database model:

CREATE TABLE [Car] (
  [ID] int NOT NULL IDENTITY,
  [Brand] nvarchar(max) NULL,
  CONSTRAINT [PK_Car] PRIMARY KEY ([ID])
);
CREATE TABLE [Driver] (
  [ID] int NOT NULL IDENTITY,
  [Name] nvarchar(max) NULL,
  CONSTRAINT [PK_Driver] PRIMARY KEY ([ID])
);
CREATE TABLE [CarDriver] (
  [CarId] int NOT NULL,
  [DriverId] int NOT NULL,
  CONSTRAINT [PK_CarDriver] PRIMARY KEY ([CarId], [DriverId]),
  CONSTRAINT [FK_CarDriver_Car_CarId] FOREIGN KEY ([CarId]) REFERENCES [Car] ([ID]) ON DELETE CASCADE,
  CONSTRAINT [FK_CarDriver_Driver_DriverId] FOREIGN KEY ([DriverId]) REFERENCES [Driver] ([ID]) ON DELETE CASCADE
);
CREATE UNIQUE INDEX [IX_CarDriver_CarId] ON [CarDriver] ([CarId]);
CREATE UNIQUE INDEX [IX_CarDriver_DriverId] ON [CarDriver] ([DriverId]);

These two indexes at the end are the icing on the piece of cake. They show that EF exactly understands what's going on here.

---

Original, but updated, answer

"This can't be hard" is what I though when I read your question. But again I found that one-to-one associations are full of pitfalls. Here we go.

I assume that by 0..1 – 0..1 you mean that two objects can exist independent of each other, but may also be exclusively associated to one another.

Lets make it concrete. Car and Driver. Imagine a pool of many cars and drivers, among them CarA and a DriverA. Now suppose you want CarA to get associated to DriverA, and your implementation is that DriverA links himself to CarA. But as soon as DriverA does this, you want CarA to be for DriverA only, CarA's association is not optional any more, so it should be set as well, immediately.

How to implement that?

Option 1:

If this is the working model:

public class Car
{
    public int CarId { get; set; }
    public string Name { get; set; }
    public int? DriverId { get; set; }
    public virtual Driver Driver { get; set; }
}

public class Driver
{
    public int DriverId { get; set; }
    public string Name { get; set; }
    public int? CarId { get; set; }
    public virtual Car Car { get; set; }
}

technically, DriverA can have a foreign key to CarA and CarA a foreign key to DriverB.

Therefore, when the foreign key DriverA-CarA is established you should "simulaneously" establish the reverse foreign key CarA-DriverA. That is something you should do in code, meaning that it's a business rule. And in reality, it's not an atomic operation, so you must make sure that it's done in one database transaction.

The class model at least supports the use case, but it's too permissive. It needs to be constrained. More importantly, it won't work with EF. EF complaints about having to set a principal end. And if you do that, EF will not create a bidirectional association.

An alternative mapping was proposed here. I tried that but with two optional associations:

In the Driver's mapping configuration:

this.HasOptional(t => t.Car).WithMany().HasForeignKey(d => d.CarId);

In the Car's mapping configuration:

this.HasOptional(t => t.Driver).WithMany().HasForeignKey(c => c.DriverId);

(There is no data annotation alternative)

I found that EF only sets one foreign key value in the database when creating a new driver and car. You have to set and save both associations separately, managing your own transaction. With existing objects you still have to set both foreign keys, although this can be saved in one SaveChanges call.

Better options? Let's see...

Option 2:

This is the one-to-many association as mentioned in the link you refer to. This model needs external constraints, but creating the association is atomic. And you've still got a reference on one end and a collection on the other end. And it maps easily with EF.

Option 3:

You could create a junction table CarDriver that has two foreign keys, to Car and Driver, both of which comprise its unique primary key:

This is a regular many-to-many association. By default, EF would map this as a class model in which Car and Driver have collection properties pointing to each other, and the junction table is not mapped directly:

public class Car
{
    public int CarId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Driver> Drivers { get; set; }
}

public class Driver
{
    public int DriverId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Car> Cars { get; set; }
}

Now the creation of association is an atomic operation. It's perfectly possible to map this model with EF. The mutual references are gone, but you still can get the FirstOrDefault() of the collection properties as a surrogate reference.

But there's an important gotcha. Now each object can have any number of associated counterparts. If you create an association, you need a coded business rule which checks if the the involved objects don't have any associations yet. Maybe this option is even worse than option 2. But I mentioned it because of the next option:

Option 4

Option 3 is atomic, but it also needs external constraints. To make an association exclusive, both columns in CarDriver should have unique keys, so each car or driver can only occur once in the table. By these indexes the model implements a bidirectionally optional 1:1 association all by itself. Any code working on it has to obey the rules. Safe and sound...

In EF6, since the introduction of HasIndex, this can be achieved by this mapping:

modelBuilder.Entity<Car>().HasOptional(c => c.CarDriver).WithRequired();
modelBuilder.Entity<Driver>().HasOptional(c => c.CarDriver).WithRequired();
modelBuilder.Entity<CarDriver>().HasKey(cd => new { cd.CarId, cd.DriverId });
modelBuilder.Entity<CarDriver>().HasIndex(cd => cd.CarId).IsUnique();
modelBuilder.Entity<CarDriver>().HasIndex(cd => cd.DriverId).IsUnique();

However, since EF6 adds indexes on FK fields by default, the unique indexes are added on top of the default non-unique indexes. So it still requires manual intervention in the migration code to remove the latter.

Conclusions

Option 1 is closest to what you want. But I don't like the obligation to set both foreign keys, it's easily forgotten or ignored by future developers.

But Option 2 and 3 have even heavier requirements in terms of coded business rules that can be forgotten. And the collections are unnatural as surrogate "1" ends. Option 3 has some appeal to me because Car and Driver are completely independent in the database and the association is a record with non-nullable foreign keys (DBAs tend to like that too).

Option 4 has the same appeal, and it's the best option when multiple applications would have to implement the external constraints that need to be imposed on option 2 and 3. Also, even if coded rules are forgotten, the database constraints are a final catch. But it can't easily be implemented by EF6.