Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

This is the third post in a series that explains entity association mappings with EF Code First. I've described these association types so far: In the previous blog post we saw the limitations of shared primary key association and argued that this type of association is relatively rare and in many schemas, a one-to-one association is represented with a foreign key field and a unique constraint. Today we are going to discuss how this is done by learning about 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 figure shows the class diagram for this 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 { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }        
    public int BillingAddressId { getset; }
    public int DeliveryAddressId { getset; }
 
    public Address BillingAddress { getset; }
    public Address DeliveryAddress { getset; }
}
 
public class Address
{
    public int AddressId { getset; }
    public string Street { getset; }
    public string City { getset; }
    public string PostalCode { getset; }   
}
 
public class EntityMappingContext : DbContext
{
    public DbSet<User> Users { getset; }
    public DbSet<Address> Addresses { getset; }
}
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 and 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 tell Code First about the foreign keys. Here is the fluent API code to identify the foreign key properties:
protected override void OnModelCreating(ModelBuilder 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. CTP5 introduced a new attribute in System.ComponentModel.DataAnnotations namespace which is called ForeignKeyAttribute and we can place it on a navigation property to specify the property that represents the foreign key of the relationship:
public class User
{
    public int UserId { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }        
    public int BillingAddressId { getset; }
    public int DeliveryAddressId { getset; }       
    
    [ForeignKey("BillingAddressId")]
    public Address BillingAddress { getset; }
    
    [ForeignKey("DeliveryAddressId")]
    public Address DeliveryAddress { getset; }
}
However, we will not use this Data Annotation and will stick with our fluent API code for a reason that you'll see soon.

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 System.Data.SqlClient.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.

SQL Server and Multiple Cascade Paths

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. So it seems that Code First tried to turn on Cascade Delete for both BillingAddressId and DeliveryAddressId columns in 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". (i.e. the User table appeared twice in a list of cascading referential actions started by a DELETE). 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 our database engine, we may or may not get this exception (For example, both Oracle and MySQL let us create Cascades in this scenario.).

Overriding Code First Convention To Resolve the Problem

As you saw, Code First automatically turns on Cascade Deletes on required one-to-many associations 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 convention and switching cascade deletes off on at least one of the associations and as of CTP5, the only way to accomplish this is by using fluent API. Let's switch it off on DeliveryAddress Association:
protected override void OnModelCreating(ModelBuilder 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 not natively 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. Basically EF 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 (i.e. we cannot define a User property on the Address class).

Create a Unique Constraint To Enforce the Relationship as a Real 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 in CTP5 to have Code First create the constraints as part of its database creation process. For that we can take advantage of the new CTP5’s SqlCommand method on DbDatabase class which allows raw SQL commands to be executed against the database. The best place to invoke SqlCommand method for this purpose is inside a Seed method that has been overridden in a custom Initializer class:
protected override void Seed(EntityMappingContext context)
{
    context.Database.SqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Billing UNIQUE(BillingAddressId)");
    context.Database.SqlCommand("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 Code First will create the following database schema for us:
It is worth mentioning that we can still enforce cascade deletes for DeliveryAddress 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 Triggers 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).

Download

Click here to download and run the one-to-one foreign key association sample that we have built in this blog post.

Summary

In this blog post we learned about one-to-one foreign key associations as a better way to represent one to one relationships. However, we saw some limitations such as the need for manual creation of unique constraints and also the fact that these type of associations cannot be bidirectional, all due to the lack of unique constraint support in EF. Support for unique constraints is going to require changes to the whole EF stack and it won't happen in the RTM targeted for this year as that RTM will be layered on top of the current .NET 4.0 functionality. That said, EF team has this feature on their list for the future, so hopefully it will be supported in a later release of EF 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.

References

Published Sunday, January 23, 2011 8:22 PM by mortezam

Comments

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Tuesday, January 25, 2011 4:40 PM by mortezam
@bonder: I believe what you are looking for can be best represented by a Shared Primary Key Association where two related tables share the same primary key values (The primary key of one table is also a foreign key of the other.). For example, consider this object model:

public class Entity
{
    public int EntityId { get; set; }
    public string Name { get; set; }

    public Book Book { get; set; }
}

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }

    public Entity Entity { get; set; }
}

public class Ctp5Context : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Entity> Entities { get; set; }
}

As a result, Code First will add a foreign key constraint which links the primary key of the Books table to the primary key of the Entities table which enforces a strong 1:1 relationship. If this is what you want to achieve, then please take a look at my other article on Shared Primary Key Associations with Code First. I hope I didn’t misunderstand your question, but if did then please provide me with some more info about your domain model and I’ll be more than happy to help out. Also thanks for your comment; I’m glad you found it useful :)

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Tuesday, February 1, 2011 7:38 PM by fred

Your solution is very clever, and I enjoy read your blog!

But, the association has Address as the primary end. That means that when you delete Address, it will cascade delete the associated User.

However it is desirable for User to be the primary end, so that when you delete a User, it will delete the associated Address. How to do that?

My only guess is to model a many:many so you can choose the primary end to be User, and then "pretend" the other side is a collection of only one Address (rather than many Addresses).

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Wednesday, February 2, 2011 7:47 PM by fred

Yes the trigger idea is good!

I will make Address optional/nullable, but is that necessary? Can't you have it as required?

PS you maybe update article to show ".WillCascadeOnDelete(false)" for BOTH Addresses, otherwise you get a bad surprise when you delete an Address and the User is automatically deleted.

PS there is another "gotcha". In my scenario instead of your User->Address entities I have instead Project->Manager entities, where Manager is TPT "inherited table" from User (there is also Employee, Supervisor, Contractor, etc.) So in my trigger code I have to delete Manager entity AND User entity, because EF does not automatically set cascade delete on TPT relationships (the deletions are done by EF, not by database). If you don't do this, you get orphaned records. For TPC/TPH should not be a problem. If you update your article in the future, maybe show this briefly for benefit of readers.

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Friday, February 4, 2011 2:20 PM by mortezam
@fred: No, it’s not necessary; of course you can cascade delete an address by defining a delete trigger on User table without having to make the associations optional. The only reason I said that was because you didn’t seem to be very happy with the fact that a User gets deleted as a result of deleting an Address and wanted to have the cascades switched off on both associations. But switching cascade off on a required association has an important consequence on deleting the principal end: we can no longer delete an Address that has a User referencing it unless we either delete the referencing user or have it reference another address because for example User.DeliveryAddressId cannot be null. In other words, a User cannot live without an Address. However, if we make the associations optional, then we can just remove an Address and update its User to simply not referencing it anymore (since now we can do this: User.DeliveryAddressId = null). I think my original answer was not clear enough, I will update it to avoid any confusion in this regard. Thanks :)

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Thursday, February 10, 2011 12:21 PM by Dan Ludwig

Awesome series. The only part I didn't like was on this one, where you say it's the last :(

I second the notion if you writing a book. I would buy it. Your writing style is easy to follow and you explain the concepts well.

I was particularly interested in one of the comments where the person was trying to implement the Layer Supertype pattern (a.k.a. public abstract class BaseEntity). I'm doing the same thing, but I'm uneasy choosing TPC for the mapping because of the bugs and problems you mentioned, at least until there is a non-CTP release.

Looking forward to reading more of your ramblings. Is this the best blog to add to my feed reader?

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Saturday, February 12, 2011 9:29 PM by mortezam
@Dan Ludwig: Thank you for your nice comment; I appreciate you reading the articles :)
I am not sure I understand your question “Is this the best blog to add to my feed reader?”, but this blog is the only place that I publish my articles.

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Saturday, February 19, 2011 7:04 PM by Search Engine Optimization Seattle

DUDE! you are the man. I've been working on a project and using the asp.net MVC 2 Music store as a guide, but then I ran into this issue as I'm utilizing CTP5 to create the database whereas they're using an already created database (and maybe for this lack of functionality in CTP5)... anyways thank you SOOOOOOOOO much I'm soooo grateful!

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Monday, February 21, 2011 6:24 PM by mortezam
@Search Engine Optimization Seattle: You're very welcome and thanks for your kind words, it is such a pleasure to hear that this blog post could help you in your Code First development :)

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Thursday, December 8, 2011 6:08 AM by dannz

Thank you very much for your wonderful post, and keep on posting such useful things !!!

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Wednesday, July 18, 2012 3:28 PM by joekahl

I am using database first and the DbContext T4s.

I see the T4s creates code exactly like the code first approach.  The difference is that is regenerates it everytime I run the T4s.  So I can take advantage of the partial classes and add foreign keys to my own partial class files which are not clobbered by the code gen.

I have followed the article and my app builds and runs, until I try a "join":

My partial:

public partial class fund_summary_vw

{

 public virtual assoc_fund_assn assoc_fund_assn { get; set; }

}

public partial class assoc_fund_assn

{

 public fund_summary_vw fund_summary_vw { get; set; }

}

List<fund_summary_vw> fund_summary_vw_list =

 posManContext.fund_summary_vw

 .Where(fsv => fsv.assoc_fund_assn.assoc_prsn_id == associateUserId)

 .ToList();

And I receive this:

The specified type member 'assoc_fund_assn' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

I am bummed that I follow so many articles and I still get left with problems. Not your fault, but new data access techniques have their pros and cons, but mostly it is tough to get a comprehensive view.

Joe

# re: Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations

Saturday, March 2, 2013 11:04 PM by Mohammed

For some reason this fix does not work for me.  I still get the annoying error message "Introducing FOREIGN KEY constraint .....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."

I have tried both the following fixes, but it appears like something has been cached somewhere as these below "fixes" are not being hit. I looked at the generated SQL, and noticed that the resulting SQL command does not change regardless of these fixes. How can I force it to generate new SQL?

       protected override void OnModelCreating(DbModelBuilder modelBuilder)

       {

           modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

       }

       protected override void OnModelCreating(DbModelBuilder modelBuilder)

       {

           modelBuilder.Entity<Travel>().HasRequired(m => m.Country).WithMany().HasForeignKey(m => m.CountryID).WillCascadeOnDelete(false);

       }

These are my classes

   public class Travel

   {

       public int CountryID { get; set; }

       public int CountryDestID { get; set; }

       public virtual Country Country { get; set; }

       public virtual Country CountryDest { get; set; }

  }

   public class Country

   {

       public int ID { get; set; }

       public string CountryName { get; set; }

   }

If anyone can help me I would greatly appreciate it.

Thanks

Mohammed