Adding 0..1 relation in EF code first

Question

I have the following models:

public class ItemRental
{
    [Key]
    public Int32 ItemRentalId { get; set; }

    public Int32 ItemId { get; set; }
    public Int32 ChargeItemId { get; set; }

    [ForeignKey("ItemId")]
    public Item Item { get; set; }

    [ForeignKey("ChargeItemId")]
    public Item ChargeItem { get; set; }
}

public class Item
{
    [Key]
    public Int32 ItemId { get; set; }

    public Int32? ItemRentalId { get; set; }

    [ForeignKey("ItemRentalId")]
    public ItemRental ItemRental { get; set; }
}

ItemRental has a 1..N relation with Item AND has a 1..N relation with ChargeItem.

Problem is that I needed a relation from Item back to the ChargeItem, so added the property ItemRentalId on the Item. This is nullable because not every Item has to have an ItemRental.

Is it possible to create this relation with just annotations?

I tried the fluent api:

       modelBuilder.Entity<Item>()
                    .HasOptional(m => m.ItemRental)
                    .WithRequired(c => c.ChargeItem)
                    .Map(p => p.MapKey("ItemRentalId"));

But after doing a migration is it not using the ChargeItemId as a relation.

The problem is when I run this migration it doesn't honor the ItemRentalId as a FK navigation property.

Answer 1

So if I understand correctly, your problem is with mapping the one to zero-or-one relationship. What you are experiencing is a by-design feature of Entity Framework. Handling one-to-one relationships (and their optional counterparts) is tricky for a lot of reasons. When you do it like this, you cannot specify the foreign key in your model — instead the primary key of your entity will also be the foreign key to the principal end, no extra FK can be specified.

See below for more details on this.

---

Mapping one-to-zero or one

So let's say that you have the following model:

public class Person
{
  public int PersonId { get; set; }
  public string Name { get; set; }
}

public class Car
{
  public int CarId { get; set; }
  public string LicensePlate { get; set; }
}

public class MyDemoContext : DbContext
{
  public DbSet<Person> People { get; set; }
  public DbSet<Car> Cars { get; set; }
}

And now you want to set it up so that you can express the following specification: one person can have one or zero car, and every car belongs to one person exactly (relationships are bidirectional, so if CarA belongs to PersonA, then PersonA 'owns' CarA).

So let's modify the model a bit: add the navigation properties and the foreign key properties:

public class Person
{
  public int PersonId { get; set; }
  public string Name { get; set; }
  public int CarId { get; set; }
  public virtual Car Car { get; set; }
}

public class Car
{
  public int CarId { get; set; }
  public string LicensePlate { get; set; }
  public int PersonId { get; set; }
  public virtual Person Person { get; set; }
}

And the configuration:

public class CarEntityTypeConfiguration : EntityTypeConfiguration<Car>
{
  public CarEntityTypeConfiguration()
  {
     this.HasRequired(c => c.Person).WithOptional(p => p.Car);                        
  }
}    

By this time this should be self-explanatory. The car has a required person (HasRequired()), with the person having an optional car (WithOptional()). Again, it doesn't matter which side you configure this relationship from, just be careful when you use the right combination of Has/With and Required/Optional. From the Person side, it would look like this:

public class PersonEntityTypeConfiguration : EntityTypeConfiguration<Person>
{
  public PersonEntityTypeConfiguration()
  {
     this.HasOptional(p => p.Car).WithOptional(c => c.Person);                        
  }
}    

Now let's check out the db schema:

Look closely: you can see that there is no FK in People to refer to Car. Also, the FK in Car is not the PersonId, but the CarId. Here's the actual script for the FK:

ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Cars_dbo.People_CarId] FOREIGN KEY([CarId])
REFERENCES [dbo].[People] ([PersonId])

So this means that the CarId and PersonId foregn key properties we have in the model are basically ignored. They are in the database, but they are not foreign keys, as it might be expected. That's because one-to-one mappings does not support adding the FK into your EF model. And that's because one-to-one mappings are quite problematic in a relational database.

The idea is that every person can have exactly one car, and that car can only belong to that person. Or there might be person records, which do not have cars associated with them.

So how could this be represented with foreign keys? Obviously, there could be a PersonId in Car, and a CarId in People. To enforce that every person can have only one car, PersonId would have to be unique in Car. But if PersonId is unique in People, then how can you add two or more records where PersonId is NULL(more than one car that don't have owners)? Answer: you can't (well actually, you can create a filtered unique index in SQL Server 2008 and newer, but let's forget about this technicality for a moment; not to mention other RDBMS). Not to mention the case where you specify both ends of the relationship...

The only real way to enforce this rule if the People and the Car tables have the 'same' primary key (same values in the connected records). And to do this, CarId in Car must be both a PK and an FK to the PK of People. And this makes the whole schema a mess. When I use this I rather name the PK/FK in Car PersonId, and configure it accordingly:

public class Person
{
  public int PersonId { get; set; }
  public string Name { get; set; }        
  public virtual Car Car { get; set; }
}

public class Car
{        
  public string LicensePlate { get; set; }
  public int PersonId { get; set; }
  public virtual Person Person { get; set; }
}

public class CarEntityTypeConfiguration : EntityTypeConfiguration<Car>
{
  public CarEntityTypeConfiguration()
  {
     this.HasRequired(c => c.Person).WithOptional(p => p.Car);
     this.HasKey(c => c.PersonId);
  }
}

Not ideal, but maybe a bit better. Still, you have to be alert when using this solution, because it goes against the usual naming conventions, which might lead you astray. Here's the schema generated from this model:

So this relationship is not enforced by the database schema, but by Entity Framework itself. That's why you have to be very careful when you use this, not to let anybody temper directly with the database.