Associations in EF Code First: One-to-One Foreign Key Associations

Introducing the Revised Model In this revised version, each User always have two addresses: one billing address and another one for delivery. The following class diagram demonstrates the domain model:

One-to-One Foreign Key Association Instead of sharing a primary key, two rows can have a foreign key relationship. One table has a foreign key column that references the primary key of the associated table (The source and target of this foreign key constraint can even be the same table: This is called a self-referencing relationship.). An additional constraint enforces this relationship as a real one to one. For example, by making the BillingAddressId column unique, we declare that a particular address can be referenced by at most one user, as a billing address. This isn’t as strong as the guarantee from a shared primary key association, which allows a particular address to be referenced by at most one user, period. With several foreign key columns (which is the case in our domain model since we also have a foreign key for DeliveryAddress), we can reference the same address target row several times. But in any case, two users can’t share the same address for the same purpose. The Object Model Let's start by creating an object model for our domain:

public class User {     public int UserId { get; set; }     public string Name { get; set; }     public int BillingAddressId { get; set; }     public int DeliveryAddressId { get; set; }              public Address BillingAddress { get; set; }     public Address DeliveryAddress { get; set; } } public class Address {     public int AddressId { get; set; }     public string Street { get; set; }     public string City { get; set; }     public string ZipCode { get; set; } } public class Context : DbContext {     public DbSet<User> Users { get; set; }     public DbSet<Address> Addresses { get; set; } }

As you can see, User class has introduced two new scalar properties as BillingAddressId and DeliveryAddressId as well as their related navigation properties (BillingAddress and DeliveryAddress). Configuring Foreign Keys With Fluent API BillingAddressId and DeliveryAddressId are foreign key scalar properties representing the actual foreign key values that the relationships are established on. However, Code First will not recognize them as the foreign keys for the associations since their names are not aligned with the conventions that it has to infer foreign keys. Therefore, we need to use fluent API (or Data Annotations) to let Code First know about the foreign key properties. The following fluent API code shows how:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {     modelBuilder.Entity<User>()                 .HasRequired(a => a.BillingAddress)                 .WithMany()                 .HasForeignKey(u => u.BillingAddressId);     modelBuilder.Entity<User>()                 .HasRequired(a => a.DeliveryAddress)                 .WithMany()                 .HasForeignKey(u => u.DeliveryAddressId); }

Alternatively, we can use Data Annotations to achieve this. EF 4.1 introduced a new attribute in System.ComponentModel.DataAnnotations namespace called ForeignKeyAttribute. We can place this on a navigation property to specify the property that represents the foreign key of the relationship:

public class User {     public int UserId { get; set; }     public string Name { get; set; }     public int BillingAddressId { get; set; }     public int DeliveryAddressId { get; set; }     [ForeignKey("BillingAddressId")]     public Address BillingAddress { get; set; }     [ForeignKey("DeliveryAddressId")]     public Address DeliveryAddress { get; set; } }

That said, we won't use this data annotation and will go with the fluent API way for a reason that you'll soon see. Creating a SQL Server Schema The object model seems to be ready to give us the desired SQL schema, however, if we try to create a SQL Server database from it, we will get an InvalidOperationException with this message:

The database creation succeeded, but the creation of the database objects did not. See InnerException for details.

The inner exception is a SqlException containing this message:

Introducing FOREIGN KEY constraint 'User_DeliveryAddress' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

As you can tell from the type of the inner exception (SqlException), it has nothing to do with EF or Code First; it has been generated purely by SQL Server when Code First was trying to create a database based on our object model. What's a Multiple Cascade Path Anyway? A Multiple Cascade Path happens when a cascade path goes from column col1 in table A to table B and also from column col2 in table A to table B. For example in our case Code First attempted to turn on cascade delete for both BillingAddressId and DeliveryAddressId columns in the Users table. In fact, Code First was trying to use Declarative Referential Integrity (DRI) to enforce cascade deletes and the problem is that SQL Server is not fully ANSI SQL-92 compliant when it comes to the cascading actions. In SQL Server, DRI forbids cascading updates or deletes in a multiple cascade path scenario. A KB article also explains why we received this error: "In SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree". And it exactly applies to our example: The User table appeared twice in a list of cascading referential actions started by a DELETE from the Addresses table. Basically, SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (cascades). Therefore, depend on your database engine, you may or may not get this exception. Overriding The Code First Convention To Resolve the Problem As you saw, Code First automatically turns on cascade delete on a required one-to-many association based on the conventions. However, in order to resolve the exception that we got from SQL Server, we have no choice other than overriding this cascade delete behavior detected by convention. Basically we need to switch cascade delete off on at least one of the relationships and as of EF 4.1, there is no way to accomplish this other than using fluent API. Let's switch it off on DeliveryAddress association for example:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {     modelBuilder.Entity<User>()                 .HasRequired(a => a.BillingAddress)                 .WithMany()                 .HasForeignKey(u => u.BillingAddressId);     modelBuilder.Entity<User>()                 .HasRequired(a => a.DeliveryAddress)                 .WithMany()                 .HasForeignKey(u => u.DeliveryAddressId).WillCascadeOnDelete(false); }

One-to-One Foreign Key Associations in EF Code First As you may have noticed, both associations in the fluent API code has been configured as a many-to-one—not one-to-one, as you might have expected. The reason is simple: Code First (and EF in general) does notnatively support one-to-one foreign key associations. In fact, EF does not support any association scenario that involves unique constraints at all. Fortunately, in this case we don’t care what’s on the target side of the association, so we can treat it like a to-one association without the many part. All we want is to express “This entity (User) has a property that is a reference to an instance of another entity (Address)” and use a foreign key field to represent that relationship. EF (of course) still thinks that the relationship is many-to-one. This is a workaround for the current EF limitation which comes with two consequences: First, EF won't create any additional constraint for us to enforces this relationship as a one to one, we need to manually create it ourselves. The second limitation that this lack of support impose to us is more important: one to one foreign key associations cannot be bidirectional (e.g. we cannot define a property for the User on the Address class). Create a Unique Constraint To Enforce the Relationship as a One to One We can manually create unique constraints on the foreign keys in the database after Code First creates it for us but if you are like me and prefer to create your database in one shot then there is a way to have Code First create the constraints as part of its database creation process. For that we can take advantage of the new EF 4.1 ExecuteSqlCommand method on Database class which allows raw SQL commands to be executed against the database. The best place to invoke ExecuteSqlCommand method for this purpose is inside a Seed method that has been overridden in a custom initializer class:

protected override void Seed(Context context) {     context.Database.ExecuteSqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Billing UNIQUE(BillingAddressId)");     context.Database.ExecuteSqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Delivery UNIQUE(DeliveryAddressId)"); }

This code adds unique constraints to the BillingAddressId and DeliveryAddressId columns in the DDL generated by Code First. SQL Schema The object model is ready now and will result in the following database schema:

It is also worth mentioning that we can still enforce cascade deletes for the Delivery Address relationship. SQL Server allows enforcing referential integrity in two different ways. DRI that we just saw is the most basic yet least flexible way. The other way is to use Triggers. We can write a Delete Trigger on the primary table that either deletes the rows in the dependent table(s) or sets all corresponding foreign keys to NULL (In our case the foreign keys are Non-Nullable so it has to delete the dependent rows). Source Code Click here to download the source code for the one-to-one foreign key association sample that we have built in this post.

Summary In this post we learned about one-to-one foreign key associations as a better way to create one to one relationships. We saw some limitations such as the need for manual creation of unique constraints and also the fact that this type of association cannot be bidirectional, all due to the lack of unique constraint support in EF. The good news is that the ADO.NET team is working on enabling unique constraints in EF but support for unique constraints requires changes to the whole EF stack which won't happen until the next major release of EF (EF 4.1 is merely layered on top of the current .NET 4.0 functionality) and until then the workaround that I showed here is going to be the way to implement one-to-one foreign key associations in EF Code First.