Associations in EF Code First: Part 3 – Shared Primary Key Associations

Thursday, April 14, 2011

This is the third post in a series that explains entity association mappings with EF Code First. This series includes:

Part 1 – Introduction and Basic Concepts
Part 2 – Complex Types
Part 3 – Shared Primary Key Associations
Part 4 – Table Splitting
Part 5 – One-to-One Foreign Key Associations
Part 6 – Many-valued Associations

In the previous blog post I demonstrated how to map a special kind of one-to-one association—a composition with complex types. We argued that the relationship between User and Address is best represented with a complex type mapping and we saw that this is usually the simplest way to represent one-to-one relationships but comes with some limitations.

In today’s blog post I’m going to discuss how we can address those limitations by changing our mapping strategy. This is particularly useful for scenarios that we want a dedicated table for Address, so that we can map both User and Address as entities. One benefit of this model is the possibility for shared references— another entity class (let’s say Shipment) can also have a reference to a particular Address instance. If a User has a reference to this instance, as her BillingAddress, the Address instance has to support shared references and needs its own identity. In this case, User and Address classes have a true one-to-one association.

Introducing the Revised Model

In this revised version, each User could have one BillingAddress (Billing Association). Also a Shipment always needs a destination address for delivery (Delivery Association). The following shows the class diagram for this domain model (note the multiplicities on association lines):

In this model we assumed that the billing address of the user is the same as her delivery address. Now let’s create the association mappings for this domain model. There are several choices, the first being a One-to-One Primary Key Association.

Shared Primary Key Associations

Also know as One-to-One Primary Key Associations, means two related tables share the same primary key values. The primary key of one table is also a foreign key of the other. Let’s see how we can create a primary key association mapping with Code First.

How to Implement a One-to-One Primary Key Association with Code First

First, we start with the POCO classes. As you can see, we've defined BillingAddress as a navigation property on User class and another one on Shipment class named DeliveryAddress. Both associations are unidirectional since we didn't define related navigation properties on Address class as for User and Shipment.

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
 
    public virtual Address BillingAddress { 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 Shipment
{
    public int ShipmentId { get; set; }     
    public string State { get; set; }
 
    public virtual Address DeliveryAddress { get; set; }
}
 
public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Address> Addresses { get; set; }
    public DbSet<Shipment> Shipments { get; set; }
}

How Code First Sees the Associations in our Object Model: One-to-Many

Code First reads the model and tries to figure out the multiplicity of the associations. Since the associations are unidirectional, Code First takes this as if one Address has many Users and Many Shipments and will create a one-to-many association for each of them. In other words, a unidirectional association is always inferred as One-to-Many by Code First. So, what we were hoping for —a one-to-one association, is not inline with the Code First conventions.

How to Change the Multiplicity of the Associations to One-to-One by Using the Conventions

Obviously, one way to turn our associations to one-to-one is by making them bidirectional. That is, adding a new navigation property to Address class of type User and another one of type Shipment. By doing that we simply signal Code First that we are looking to have one-to-one associations since for example User has an Address and also Address has a User. Therefore, Code First will change the multiplicity to one-to-one and this will solve the problem.

Should We Make the Associations Bidirectional?

As always, the decision is up to us and depends on whether we need to navigate through our objects in that direction in the application code. In this case, we’d probably conclude that the bidirectional association doesn’t make much sense. If we call anAddress.User, we are saying “give me the user who has this address”, not a very reasonable request. So this is not a good option. Instead we'll keep our object model as it is and will explicitly ask Code First to make our associations one-to-one.

How to Change the Multiplicity to One-to-One with Fluent API

The following code is all that is needed to make the associations to be one-to-one. Note how the multiplicities in the UML class diagram (e.g. 1 on User and 0..1 on address) has been translated to the fluent API code by using HasRequired and HasOptional methods:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional();
}

Also it worth noting that when we are mapping a one-to-one association with fluent API, we don't need to specify the foreign key as we would do when mapping a one-to-many association with HasForeignKey method. Since EF only supports one-to-one associations on primary keys, it will automatically create the relationship in the database on the primary keys.

Database Schema

The mapping result for our object model is as follows (note the Identity column on Users table):

Referential Integrity

In relational database design the referential integrity rule states that each non-null value of a foreign key must match the value of some primary key. But wait, how does it even applies here? All we have is just three primary keys referencing each other! Who is the primary key and who is the foreign key? The best way to find the answer of this question is to take a look at the properties of the relationships in the database that has been created by Code First:

As you can see, Code First adds a foreign key constraint which links the primary key of the Addresses table to the primary key of the Users table and adds another foreign key constraint that links the primary key of the Shipments table to the primary key of the Addresses table. The foreign key constraint means that a user has to exist for a particular address but not the other way around. In other words, the database guarantees that an Addresses row’s primary key references a valid Users primary key and a Shipments row’s primary key references a valid Addresses primary key.

How Code First Determines the Principal and Dependent Ends in an Association?

Code First has rules to determine the principal and dependent ends of an association. For one-to-many relationships the many end is always the dependent, but it gets a little tricky in one-to-one associations. In one-to-one associations Code First decides based on our object model, and possible data annotations or fluent API code that we may have. For example in this case, we used the following fluent API code to configure the User-Address association:

modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress).WithRequired();

This reads as "User entity has an optional association with one Address object but this association is required for Address entity". For Code First this is good enough to make the decision: It marked User as the principal end and Address as the dependent end in the association. Since we have the same fluent API code for the second association between Address and Shipment, it marks Address as the principal end and Shipment as the dependent end in this association as well.

This decision has some consequences. In fact, the referential integrity that we saw, is the first result of this Code First's principal/dependent decision.

Second Result of Code First's Principal/Dependent Decision: Database Identity

If you take a closer look at the above DB schema, you'll notice that only UserId has a regular identifier generator (aka Identity or Sequence) and AddressId and ShipmentId does not. This is a very important consequence of the principal/dependent decision for one-to-one associations: the dependent primary key will become non-Identity by default. This make sense because they share their primary key values and only one of them can be auto generated and we need to take care of providing valid keys for the rest.

What about Cascade Deletes?

As we saw, each Address always belongs to one User and each Shipment always delivered to one single Address. We want to make sure that when we delete a User the possible dependent rows on Address and Shipment also get deleted in the database. In fact, this is one of the Referential Integrity Refactorings which called Introduce Cascading Delete. The primary reason we would apply "Introduce Cascading Delete" is to preserve the referential integrity of our data by ensuring that related rows are appropriately deleted when a parent row is deleted. By default, Code First does not enable cascade delete when it creates a one-to-one relationship in the database. As always we can override this convention by fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired()
                               .WillCascadeOnDelete();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional()
                                   .WillCascadeOnDelete();
}

What the Additional Methods Like WithRequiredDependent are for?

The HasRequired method returns an object of type RequiredNavigationPropertyConfiguration which defines two special methods called WithRequiredDependent and WithRequiredPrincipal in addition to the typical WithMany and WithOptional methods that we usually use. We saw that the only reason Code First could figure out principal and dependent in our associations was because our fluent API code clearly specified one end as Required and the other as Optional. But what if both endpoints are required or both are optional in the association? For example consider a scenario that a User always has one Address and Address always has one User (required on both end). Now Code First cannot pick up the principal and dependent ends on its own and that's exactly where methods like WithRequiredDependent come into play. In other words, this scenario ultimately need to be configured by fluent API and fluent API is designed in a way that will force you to explicitly specify who is dependent and who is principal in a required-required or optional-optional association scenario.

For example, this fluent API code shows how we can configure the User-Address association where both ends are required:

modelBuilder.Entity<User>().HasRequired(u => u.BillingAddress).WithRequiredDependent();

Taking a closer look at the RequiredNavigationPropertyConfiguration type also shows the idea:

public class RequiredNavigationPropertyConfiguration<TEntityType, TTargetEntityType>
{
    public DependentNavigationPropertyConfiguration<TEntityType, TTargetEntityType> WithMany();
    public CascadableNavigationPropertyConfiguration WithOptional();
    public CascadableNavigationPropertyConfiguration WithRequiredDependent();
    public CascadableNavigationPropertyConfiguration WithRequiredPrincipal();
}

As you can see, if you want to go another Required after HasRequired method, you have to either call WithRequiredDependent or WithRequiredPrincipal since there is no WithRequired method defined on RequiredNavigationPropertyConfiguration class.

Working with the Model

Here is an example for adding a new user along with its billing address. EF is smart enough to use the newly generated UserId for the AddressId as well:

using (var context = new Context())
{
    Address billingAddress = new Address()
    {
        Street = "Main St.",
        City   = "Seattle"
    };
                
    User user = new User()
    {
        Name = "Morteza",                    
        BillingAddress = billingAddress
    };
 
    context.Users.Add(user);
    context.SaveChanges();
}

The following code is an example of adding a new Address and Shipment for an existing User (assuming that we have a User with UserId = 1 in the database):

using (var context = new Context())
{
    Address deliveryAddress = new Address()
    {
        AddressId = 1,
        Street = "Main St.",                    
    };
 
    Shipment shipment = new Shipment()
    {
        ShipmentId = 1,
        State = "Shipped",                    
        DeliveryAddress = deliveryAddress
    };
 
    context.Shipments.Add(shipment);
    context.SaveChanges();
}

Limitations of This Mapping

There are two important limitations to associations mapped as shared primary key:

Difficulty in Saving Related Objects
Multiple Addresses for User is Not Possible

Summary

In this post we learned about one-to-one associations which shared primary key is just one way to implement it. Shared primary key associations aren’t uncommon but are relatively rare. In many schemas, a one-to-one association is represented with a foreign key field and a unique constraint. In the next posts we will revisit the same domain model and will learn about other ways to map one-to-one associations that does not have the limitations of the shared primary key association mapping.

References

25 Comments

Hi,

I think you have a tiny mistake in your post.
"assuming that we have a User with UserId=2 in the database"

Shouldn't this be "UserId=1"? Or am I missing something?

Manu.

TweeZz - Friday, April 15, 2011 6:23:42 AM

@Manu: Yes, you are correct, it should be UserId = 1. I corrected this on the post. Thank you very much! :)

mortezam - Friday, April 15, 2011 4:03:37 PM

Great article. Waiting for your next post as i am working on a mapping where i have two address fields for a user.

Satish - Friday, April 15, 2011 9:43:01 PM

Hi, "adding a new Address and Shipment for an existing User (assuming that we have a User with UserId = 1 in the database):..." Are you forget one line: "user.BillingAddress = billingAddress" or whether I ignore anything?

Johnny Fee - Tuesday, May 17, 2011 3:25:23 AM

Thanks again for great series of posts.

dencio - Wednesday, May 18, 2011 2:12:57 PM

@Johnny Fee: No, we didn’t really forget anything in there. The second code snippet has nothing to do with the previous one so there is neither a billingAddress nor a user. Like I said in the post, we know for a fact that “1” is a valid UserId in the database (let’s say we retrieved this UserId earlier from another business process) so now we can use this UserId to relate an address along with a Shipment object to it. Hope this helps.

mortezam - Friday, May 20, 2011 2:25:13 PM

Hi Manavi,
Here is my Data Model...
public class User{    [Key]    public string Email { get; set; }    public string Name { get; set; }}
public class Profile{    public int ProfileID { get; set; }    public string ProfileName { get; set; }
    public virtual User User { get; set; }    public virtual Address BillingAddress { get; set; }}
public class Address{    public string 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; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)    {        modelBuilder.Entity<Profile>().HasRequired(p => p.User).WithOptional();                 modelBuilder.Entity<Profile>().HasOptional(p => p.BillingAddress).WithRequired();    }}
If Profile ID is string, then it creates one - one mapping. But if ProfileID is int, EF is creating another column User_Email as foreign key (expected). But this time, it's Many to One between user and profile. Why do you think that is happenning?
Can't we have a new foreign key and yet have One-One?

Preetham Reddy - Wednesday, June 15, 2011 6:21:09 PM

Hi Morteza,
The entity model that you've described only works if there is no other column in Junction Table.
In my case, I need to caputure EventId too.
Users attend Events. During the event, User A can express interest in multiple users. User B can express interest in multiple users. If there is a match ( ie., if a user A like user B and user B like User A) then their contact info will be exchanged.
Here it is a self referencing Many - Many relationship but I also need to know the EventID of the event they attended. That way, I can determine where they met.
Here is my model but it is not working.
public class User{    public User()    {        UserLike = new UserLike();    }
    public int UserId { get; set; }    public string Email { get; set; }    public virtual UserLike UserLike { get; set; }}
public class UserLike{    public UserLike()    {        LikesUsers = new List<User>();    }
    public int EventId { get; set; }    public virtual Event Event { get; set; }
    [Key, ForeignKey("User")]    public int UserLikeId { get; set; }    public virtual User User { get; set; }    public virtual ICollection<User> LikesUsers { get; set; }}
Event is a simple class. What am I doing wrong?

Preetham Reddy - Sunday, June 19, 2011 9:26:18 AM

@Preetham Reddy: As I explained in the sixth part of this series, a many-to-many association cannot have a payload (e.g EventId), and if that’s the case then we have to break it down to two one-to-many associations to an intervening class and I can see you’ve correctly created this class (UserLike) to represent the extra information attached to your self-referencing many-to-many association but the associations from this intermediate class are not correct as we need to define exactly 2 many-to-one association from UserLike to User like I showed in the following object model:
public class User{            public int UserId { get; set; }    public string Email { get; set; }       
    public virtual ICollection<UserLike> ThisUserLikes { get; set; }    public virtual ICollection<UserLike> UsersLikeThisUser { get; set; }}       
public class UserLike{    public int UserLikeId { get; set; }    public int LikerId { get; set; }    public int LikeeId { get; set; }    public int EventId { get; set; }
    public User Liker { get; set; }    public User Likee { get; set; }    public virtual Event Event { get; set; }}
public class Event{    public int EventId { get; set; }    public string Name { get; set; }}  
public class Context : DbContext {    public DbSet<User> Users { get; set; }     public DbSet<Event> Events { get; set; }     protected override void OnModelCreating(DbModelBuilder modelBuilder)    {        modelBuilder.Entity<User>()                    .HasMany(u => u.ThisUserLikes)                    .WithRequired(ul => ul.Liker)                    .HasForeignKey(ul => ul.LikerId);
        modelBuilder.Entity<User>()                                            .HasMany(u => u.UsersLikeThisUser)                    .WithRequired(ul => ul.Likee)                    .HasForeignKey(ul => ul.LikeeId)                    .WillCascadeOnDelete(false);    }}

mortezam - Sunday, June 19, 2011 7:40:47 PM

Awesome... I've got my model working now...

Preetham Reddy - Monday, June 20, 2011 4:32:50 AM

@Preetham Reddy: One last thing before you go, you can use the following LINQ query to retrieve all the users who like each other:
using (var context = new Context()){                    var friends = (from u1 in context.Users                       from likers in u1.UsersLikeThisUser                       from u2 in u1.ThisUserLikes                        where u2.LikeeId == likers.LikerId                       select new                       {                           OurUser = u1.UserId,                           HerFriend = u2.LikeeId                        })                       .ToList();}

mortezam - Monday, June 20, 2011 4:41:38 AM

Hi,

What about the case where a User can have a list of Addresses but an Address can only belong to a single User? As with the above [original] example, an Address can be associated to a Shipment. I was thinking of modelling this through a association table (e.g. UserAddress which has UserId and AddressId; ShipmentAddress which has ShipmentId and AddressId). Would very much appreciate any guidance on how to model this in EF.

Thanks,

Tony

Tony - Monday, June 20, 2011 8:08:22 AM

@Tony: You don’t really need to create a join table like UserAddress unless you require a many-to-many association between your entities. The scenario you described can be mapped with a simple one-to-many association between User and Address as I showed in the following object model:
public class User{    public int UserId { get; set; }    public string Name { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }}
public class Address{    public int AddressId { get; set; }            public int UserId { get; set; }
    public User User { get; set; }}
public class Context : DbContext{    public DbSet<User> Users { get; set; }    public DbSet<Address> Addresses { get; set; }}Note that we didn’t even need fluent API code for this as everything will be picked up by convention. You can configure the same for the association between Address and Shipment entities if a 1:* relationship is required between them. Please take a look at the last part of this series where I explained many-valued associations in detail. Hope this helps.

mortezam - Monday, June 20, 2011 2:57:11 PM

Thanks Mortezem.
Sorry I wasn't clear.  I'll give my exact example instead...
I have the following classes...
public class Contact {   public int Id  { get; set; }   <some other Contact details here>}
public class BusinessUnit {   public int Id { get; set; }   public List<Contact> Contacts  { get; set; }   <some other Business Unit details here>}
public class Counterparty {   public int Id { get; set; }   public List<Contact> Contacts  { get; set; }   <some other Counterparty details here>}
Business Unit and Counterparty (and possibly some other Entities in the future) can have an arbitary number of Contacts hence I am not able to put an inverse key (i.e. BusinessUnitId + BusinessUnit, CounterpartyId + Counterparty) on the Contact class.  
Thanks heaps.
Tony

Tony - Monday, June 20, 2011 10:50:33 PM

@Tony: Ok, if that’s the case then using an associative table to map a one-to-many association would make sense. In order to create this mapping, you need to set up a many-to-many association between Contact and BusinessUnit (same thing for Counterparty as well). The following fluent API code shows how:
public class Contact {   public int Id  { get; set; }}
public class BusinessUnit {   public int Id { get; set; }   public ISet<Contact> Contacts  { get; set; }}
public class Counterparty {   public int Id { get; set; }   public ISet<Contact> Contacts { get; set; }}
public class Context : DbContext{    public DbSet<Contact> Contacts { get; set; }    public DbSet<BusinessUnit> BusinessUnits { get; set; }    public DbSet<Counterparty> Counterparties { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)    {        modelBuilder.Entity<BusinessUnit>()                    .HasMany(u => u.Contacts)                    .WithMany()                    .Map(c =>                    {                        c.MapLeftKey("BusinessUnitId");                        c.MapRightKey("ContactId");                        c.ToTable("BusinessUnitContact");                    });
        modelBuilder.Entity<Counterparty>()                    .HasMany(u => u.Contacts)                    .WithMany()                    .Map(c =>                    {                        c.MapLeftKey("CounterpartyId");                        c.MapRightKey("ContactId");                        c.ToTable("CounterpartyContact");                    });    }}After running this object model you need to enforce the relationships as a real one-to-many. For that you have to go into your database and manually create unique constraints on ContactId in both join tables (CounterpartyContact and BusinessUnitContact). In other words, you want to make sure that for example once a BusinessUnit references a Contact, this Contact won’t be referenced by any other BusinessUnit. That said, this design still has a problem: the data model allows a single Contact to be referenced by a BusinessUnit and a Counterparty at the same time. If that’s an issue then we have to come up with other ways to map the association between Contact and other entities, ways that ensures a Contact would be referenced by one single entity at any time.

mortezam - Wednesday, June 22, 2011 1:22:33 AM

@mortezam,

ABSOLUTELY BRILLIANT!!!

That's fixed my problem and you've saved me a lot of time with this.

re: "The Data Model allows a single Contact to be referenced by a BusinessUnit and a Counterparty" at the same time" issue, I'll have a think about it on my end. I'm sure I'll be able to come up with a solution but would more than welcome any tips you can provide.

Thank you so much for your help. It is very much appreciated!

Tony

Tony - Wednesday, June 22, 2011 6:40:16 AM

I 'm a little confused, i think the modelBuilder.Entity().HasRequired(u => u.BillingAddress).WithRequiredDependent() should be modelBuilder.Entity().HasRequired(u => u.BillingAddress).WithRequiredPrincipal()

i mean here we should use WithRequiredPrincipal() instead of WithRequiredDependent()

please help to clarify,thanks!

ddredstar@hotmail.com - Wednesday, June 29, 2011 2:49:09 AM

class tree
{
    int id;
    string title;
}
class file
{
    int id;
    string FileName;
    public virtual tree Tree;
}

file f = db.file.Find(id);

f.tree.title is ALWAYS NULL ,

why the tree instance is not loaded with the file ?

yaron - Monday, August 22, 2011 2:10:38 PM

@yaron: First make sure that tree and every other class member on both entities is a property and not a field, since EF does not support fields yet. For example, your file entity should be look like this:

class file
{
    public int id { get; set; }
    public string FileName { get; set; }
    public virtual tree Tree { get; set; }
}

Now if you are reading the f.tree.title while your DbContext instance is not yet disposed then lazy loading should kick in and retrieve the tree property for you, but if you are reading the f.tree.title when the DbContext is already disposed then you need to eager load the Tree property when retrieving the file entity. One way to eager load a navigation property is to use the Include method:

file f = db.file.Where(f => f.id == id).Include(f => f.Tree).Single();

mortezam - Sunday, August 28, 2011 2:29:20 AM

Hello Morteza,

I happen to be working on a similar database design. By following your way, I was able to get one step closer. However, now I’m stuck and I’m in dire need of help.

Here’s my scenario: I have 2 tables in a 1:1 relationship, UserLogin and UserProfile.

UserLogin consists of UserID set to autoincrement ON whereas UserProfile consists of UserID with autoincrement OFF.

I have also overridden OnModelCreating by adding the following 2 lines:

modelBuilder.Entity<UserProfile>().HasKey(u => u.UserID);

modelBuilder.Entity<UserLogin>().HasOptional(u => u.UserProfile).WithRequired();

By right, this should put them in a 1:1 relationship. However, this is the error I get:

{"Invalid column name 'UserProfile_UserID'."}

Am I missing something?

In UserLogin class, I have the following:

[Key()]
public long UserID { get; set; }
public virtual UserProfile UserProfile { get; set; }

And in UserProfile class, I have this:

public long UserID { get; set; }

Jon - Friday, September 16, 2011 11:38:30 PM

Just noticed at the end of the article (2nd limitation) that you stated, "in the current setup, each row in the User table has a corresponding row in the Address table." That isn't true since you have a 1 -> 0...1 relationship for User -> Address, right? Just want to be clear.

Great article by the way! Best code-first article on the internet IMO.

Chase - Tuesday, November 15, 2011 1:07:55 AM

@Chase: You are correct and I changed the text a little bit so that it better reflects the model we discussed in this article. Thank you so much for your comment! :)

mortezam - Wednesday, November 16, 2011 3:27:19 PM

This is exactly the kind of article I was looking for. Great work.

Yasir Atabani - Monday, November 28, 2011 7:55:22 PM

what's the meaning of
classA_ClassB_Source and ClassA_ClassB_Target
hard to found you blog .please help

newer - Saturday, February 25, 2012 7:07:46 PM

Great stuff. You saved me quite a bit of heart-ache. Please keep up the excellent work.

poundingCode - Wednesday, March 7, 2012 7:38:04 PM

Comments have been disabled for this content.