Entity Framework Code First: Relationship mapping

I guess there's really no point in stating again how much I think EF CF is awesome (just check this post or this post).
There's a lot of magic in EF CF — and of course there are more straightforward things. Mapping entities is one of the plainer and simpler things.

Mapping entities

So let's say you have two different entities, something like this:

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 then you want to express some 'has-a' type of relationship between the two entity types. Things like:

• One person can have any number of cars and one car belongs to one person, or maybe there are cars without a person.
• One person can have any number of cars and one car can have more than one owners.
• Every person can have one or zero cars, and every car belongs to one person, or optionally, to zero people.

These are the possible options (in every case the relationship is bidirectional, though; so if a car belongs to a person, than that person belongs to the car also).

Mapping one-to-many

My first example, where everybody can have any number of cars (meaning zero, one or more) and one car belongs to one person, can be specified in EF quite simply. Just modify your model classes:

public class Person
{
   public int PersonId { get; set; }
   public string Name { get; set; }
   public virtual ICollection<Car> Cars { get; set; } // don't forget to initialize (use HashSet)
}

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 that's it :) You already have your relationship set up. In the database, this is represented with foreign keys, of course.

This model already supports a person not having a car, but it doesn't support a car without a person. To change this, simply change the type of PersonId to int?.

Note that the PersonId column is now nullable in the Cars table.

How does EF know which column is the foreign key and where should it point to? The conventions, of course. Having a property of type Person that is named Person with a PersonId property leads EF to conclude that PersonId is a foreign key, and it points to the primary key of the table represented by the type Person.

But what if you were to change PersonId to OwnerId and Person to Owner in the Car type?

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

Well, unfortunately in this case, the conventions are not enough to produce the correct DB schema:

No worries; you can help EF with some hints about your relationships and keys in the model. Simply configure your Car type to use the _OwnerId property as the FK. Create an entity type configuration and apply it in your OnModelCreating:

 public class CarEntityTypeConfiguration : EntityTypeConfiguration<Car>
{
  public CarEntityTypeConfiguration()
  {
     this.HasRequired(c => c.Owner).WithMany(p => p.Cars).HasForeignKey(c => c.OwnerId);
  }
}

This basically says that Car has a required property, Owner (HasRequired()) and in the type of Owner, the Cars property is used to refer back to the car entities (WithMany()). And finally the property representing the foreign key is specified (HasForeignKey()). This gives us the schema we want:

You could configure the relationship from the Person side as well:

public class PersonEntityTypeConfiguration : EntityTypeConfiguration<Person>
{
  public PersonEntityTypeConfiguration()
  {
    this.HasMany(p => p.Cars).WithRequired(c => c.Owner).HasForeignKey(c => c.OwnerId);
  }
}

The idea is the same, just the sides are different (note how you can read the whole thing: 'this person has many cars, each car with a required owner'). Doesn't matter if you configure the relationship from the Person side or the Car side. You can even include both, but in this case be careful to specify the same relationship on both sides!
Note: if the relationship was optional on the car side, you would use HasOptional() and WithOptional(), respectively. In this case be careful to specify the column of the foreign key as a nullable type!

Mapping many-to-many

Let's move on to the other scenario, where every person can have multiple cars and every car can have multiple owners. This is a many-to-many relationship. The easiest way is to let EF do it's magic using conventions. Just change the model like this:

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

public class Car
{
   public int CarId { get; set; }
   public string LicensePlate { get; set; }        
   public virtual ICollection<Person> Owners { get; set; }
}

And the schema:

Almost perfect. As you can see, EF recognized the need for a join table, where you can keep track of person-car pairings. Quite simple, isn't it? You might want to rename the fields in the join table to be a little more friendly. You can do this by using the usual configuration methods (again, it doesn't matter which side you do the configuration from):

public class CarEntityTypeConfiguration : EntityTypeConfiguration<Car>
{
   public CarEntityTypeConfiguration()
   {
      this.HasMany(c => c.Owners).WithMany(p => p.Cars)
          .Map(m =>
              {
                 m.MapLeftKey("OwnerId");
                 m.MapRightKey("CarId");
                 m.ToTable("PersonCars");
              }
        );
  }
}

Quite easy to read even: this car has many owners (HasMany()), with each owner having many cars (WithMany()). Map this so that you map the left key to OwnerId (MapLeftKey()), the right key to CarId (MapRightKey()) and the whole thing to the table PersonCars (ToTable()). And this gives you exactly that schema:

I have to admit, I'm not really a fan of this solution. You cannot track extra information to a person-car association (let's say the date from which it is valid), because you can't modify the table.

Also, the CarId in the join table is part of the primary key, so if the family buys a new car, you have to first delete the old associations and add new ones. EF hides this from you, but this means that you have to do these two operations instead of a simple update (not to mention that frequent inserts/deletes might lead to index fragmentation — good thing there is an easy fix for that).

In this case what you can do is create a join entity that has a reference to both one specific car and one specific person. Basically you look at your many-to-many association as a combinations of two one-to-many associations:

public class PersonToCar
{
   public int PersonToCarId { get; set; }
   public int CarId { get; set; }
   public virtual Car Car { get; set; }
   public int PersonId { get; set; }
   public virtual Person Person { get; set; }
   public DateTime ValidFrom { get; set; }
}

public class Person
{
  public int PersonId { get; set; }
  public string Name { get; set; }
  public virtual ICollection<PersonToCar> CarOwnerShips { get; set; }
}

public class Car
{
  public int CarId { get; set; }
  public string LicensePlate { get; set; }        
  public virtual ICollection<PersonToCar> Ownerships { get; set; }
}

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

This gives me much more control and it's a lot more flexible. I can now add custom data to the association and every association has its own primary key, so I can update the car or the owner reference in them.

Note that this really is just a combination of two one-to-many relationships, so you can use all the configuration options discussed above.

Mapping one-to-one

The case when one person can be associated with one car and vice versa is called a one-to-one mapping. In this mapping, the relationship is always bidirectional: if PersonA belongs to CarA, then CarA belongs to PersonA. One-to-one mapping is probably the most interesting from a technical point of view.

There are a number of different scenarios worth discussing:

• One side can be optional and one can be required.
• Both sides can be required
• Both sides can be optional

Mapping one-to-zero or one

Let's discuss the case when one side is required and one side is optional. In our case it makes more sense for the car to be optional for the person.

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 the 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 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 Car, then how can you add two or more records where PersonId is NULL (more cars which 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 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.

Mapping one-to-one exactly

Mapping one-to-one (when both sides are required) is also a tricky thing.

Let's imagine how this could be represented with foreign keys. Again, a CarId in People that refers to CarId in Car, and a PersonId in Car that refers to the PersonId in People.
Now what happens if you want to insert a Car record? In order for this to succeed, there must be a PersonId in Car, because it is required. And for this PersonId to be valid, the corresponding record in People must exist. OK, so let's go ahead and insert the person record. But for this to succeed, a valid CarId must be in the person record — but that car is not inserted yet! It cannot be, because we have to insert the referred person record first. But we cannot insert the referred person record, because it refers back to the car record, so that must be inserted first (foreign key-ception :) ).

So this cannot be represented the 'logical' way either. Again, you have to drop one of the foreign keys. Which one you drop is up to you. The side that is left with a foreign key is called the 'dependent', the side that is left without a foreign key is called the 'principal'. And again, to ensure the uniqueness in the dependent, the PK has to be the FK, so adding an FK column and importing that to your model is not supported.

So here's the configuration:

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

By now you really should have gotten the logic of it :) Just remember that you can choose the other side as well, just be careful to use the Dependent/Principal versions of WithRequired (and you still have to configure the PK in Car).

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

If you check the DB schema, you'll find that it's exactly the same as it was in the case of the one-to-one or zero solution. That's because again, this is not enforced by the schema, but by EF itself. So again, be careful :)

Mapping one or zero-to-one or zero

And to finish off, let's briefly look at the case when both sides are optional.

I just realized that this post has gotten way longer than I had anticipated :) So I'm not going into the details and play with the idea of having two FK-s and the potential problems and warn you about the dangers of not enforcing these rules in the schema but in just EF itself.

Here's the config you need to apply:

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

Again, you can configure from the other side as well, just be careful to use the right methods :)

Closing arguments

Again, this post is way longer than it should be :) And I don't have any ground-breaking wisdom on the topic anyway. As I said in the introduction, I think this is pretty straightforward, just think it through and use the right methods :)