Composite keys in EF CodeFirst

 

It is not very often a case that you need composite key in your db, but when you have such domain then you cannot make a good model if your orm tool does not support composite keys. EF CodeFirst has recently improved this support (last time I needed it it was not any good), but there are not many resources/samples out there. Here is one.

Model:

It is a simple hierarchy with no surrogate, only natural keys. Address object/table actually contains all the data as there are no additional attributes in other entities, but this is only a example, and you can easily imagine that you have additional properties in i.e. city (coordinates, or link to map). If you don’t need additional properties in your entities, then you can tell EF modelBuilder to create complex types for them and you will have composite keys automatically.

One downside of this “manual” approach is that you need to have both primary key and related object property in your entity (i.e. State has string Country_Name and Country Country properties), but that is not a big problem, and in most scenarios you will want that as you will have data that you need without join or loading another object (that is the purpose of natural keys).

This is the db schema that I want, each dependent object inherits primary keys from its principal, so when I read Address record, I already have all the data that I need, and in at least 90% of reads there is no need to do join with other tables.

This diagram is actually created from the database which was created from my model, using EF CodeFirst. To get these composite primary keys, I used mappings described below.


Mapping configuration (in DbContext.OnModelCreating):

modelBuilder.Entity<Country>().HasKey(c => c.Name)
.HasMany(c => c.States)
.WithRequired(s => s.Country)
.HasForeignKey(s => s.Country_Name);

modelBuilder.Entity<State>().HasKey(s => new { s.Country_Name, s.Name })
.HasMany(s => s.Cities)
.WithRequired(c => c.State)
.HasForeignKey(c => new { c.Country_Name, c.State_Name });
modelBuilder.Entity<City>().HasKey(c => new { c.Country_Name, c.State_Name, Name = c.Name })
.HasMany(c => c.Streets)
.WithRequired(s => s.City)
.HasForeignKey(s => new { s.Country_Name, s.State_Name, s.City_Name });
modelBuilder.Entity<Street>().HasKey(s => new { s.Country_Name, s.State_Name, s.City_Name, s.Name })
.HasMany(s => s.Addresses)
.WithRequired(a => a.Street)
.HasForeignKey(a => new { a.Country_Name, a.State_Name, a.City_Name, a.Street_Name });
modelBuilder.Entity<Address>().HasKey(a => new
{
a.Country_Name,
a.State_Name,
a.City_Name,
a.Street_Name,
a.Number,
a.ZIP,
a.Line1,
a.Line2
});

Note that EF will by default choose nvarchar(128) for primary keys. As nchar has better properties when used as primary key or index in rdbms, I configured my primary keys to be nchar:

var keytype = "nchar";
int keylength = 50;
modelBuilder.Entity<Country>().Property(c => c.Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<State>().Property(c => c.Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<State>().Property(c => c.Country_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<City>().Property(c => c.Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<City>().Property(c => c.State_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<City>().Property(c => c.Country_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Street>().Property(c => c.Name).HasColumnType(keytype).HasMaxLength(80);
modelBuilder.Entity<Street>().Property(c => c.City_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Street>().Property(c => c.State_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Street>().Property(c => c.Country_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Address>().Property(c => c.Street_Name).HasColumnType(keytype).HasMaxLength(80);
modelBuilder.Entity<Address>().Property(c => c.City_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Address>().Property(c => c.State_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Address>().Property(c => c.Country_Name).HasColumnType(keytype).HasMaxLength(keylength);
modelBuilder.Entity<Address>().Property(c => c.Number).HasColumnType(keytype).HasMaxLength(20);
modelBuilder.Entity<Address>().Property(c => c.ZIP).HasColumnType(keytype).HasMaxLength(5);
modelBuilder.Entity<Address>().Property(c => c.Line1).HasColumnType(keytype).HasMaxLength(50);
modelBuilder.Entity<Address>().Property(c => c.Line2).HasColumnType(keytype).HasMaxLength(50);