| 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 ModelIn 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 AssociationInstead 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 ModelLet's start by creating an object model for our domain: |
public class User
{
public int UserId { get; set; }
public string FirstName { get; set; }
public string LastName { 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 PostalCode { get; set; }
}
public class EntityMappingContext : 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 APIBillingAddressId 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 { get; set; }
public string FirstName { get; set; }
public string LastName { 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; }
} |
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 SchemaThe 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 PathsA 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 ProblemAs 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 FirstAs 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 OneWe 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 SchemaThe 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).
DownloadClick here to download and run the one-to-one foreign key association sample that we have built in this blog post. |
SummaryIn 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
|