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

26 Comments

• Nice article.
  
  But not able insert records.
  
  Getting error that u should provide values of parent table which is not null.
  
  Can you please tell me why this is happening.?
  

• @Prajakta: Could you please show your code that causes the exception?
  

• @Frank: Thanks. Could you please show your object model as well as the client code that adds a new UserImage to the database?
  

• POCOs:
  public class User{      public Guid UserId { get; set; } //have to use Guid as it maps to aspnet db    public int CampaignId { get; set; }    public virtual ICollection<UserImage> { get; set; }}
  public class UserImage{    public int UserImageId { get; set; }    public Guid UserId { get; set; }    public int CampaignId { get; set; }}
  public class Campaign{    public int CampaignId { get; set; }    public virtual ICollection<User> Users { get; set; }}
  User Mapping:
      HasMany(u => u.UserImages)    .WithOptional()    .WillCascadeOnDelete();
      HasMany(u => u.UserCampaigns)    .WithOptional()    .WillCascadeOnDelete();
  Campaign Mapping:
      HasKey(c => c.CampaignId)    .HasMany(c => c.Users)    .WithMany(u => u.Campaigns)    .Map(m => m.MapLeftKey("UserId"));
  No mapping for UserImage
  

• Morteza,
  
  So the user object has a campaigns collection and in my case, it will contain one campaign in the collection. The UserImage has a property called CamapaignId. Would I have to set that manually? If so, any other way I can remodel my classes so the CampaignId gets set automatically when adding a UserImage?
  

• @Frank: Your fluent API code doesn’t match your object model, but I still got the idea. Yes, adding a new UserImage to the UserImages collection wouldn’t cause UserImage.CampaignId to be populated since the association between User and UserImage has nothing to do with the association between UserImage and Campaign classes. In fact, I don’t see any reason to have yet another association between UserImage and Campaign entites by defining a CampaignId property on UserImage class because once you have a UserImage object you can simply access the Campaign information by accessing it on the related User (something like userImage.User.UserCampaign). Therefore, I would design the object model slightly different:
  
  
  
  public class User
  { 
      public Guid UserId { get; set; }
      public int? CampaignId { get; set; }
  
  
      public virtual Campaign UserCampaign { get; set; }
      public virtual ICollection<UserImage> UserImages { get; set; }
  }
  
  
  
  public class UserImage
  {
      public int UserImageId { get; set; }
      public Guid UserId { get; set; }
      public User User { get; set; }
  }
  
  
  
  public class Campaign
  {
      public int CampaignId { get; set; }       
  }           
  
  
  
  public class Context : DbContext
  {
      public DbSet<User> Users { get; set; }
      public DbSet<UserImage> UserImages { get; set; }
      public DbSet<Campaign> Campaigns { get; set; }
  }
  
  
  
  And then you can work with the object model like the following code:
  
  
  
  using (var context = new Context())
  {
      UserImage userImage = new UserImage()
      {
          User = new User()
          {
              UserCampaign = new Campaign()
          }
      };
  
  
  
      context.UserImages.Add(userImage);
      context.SaveChanges();               
  }
  
  
  
  
  You can also use the method I showed in this post to enforce a one to one relationship between User and UserCampaign entities. Hope this helps.
  

• Thank you for taking the time. I got you and it makes sense assuming you only have 1:1 relationship. What I was trying to say (unsuccessfully) is that there could be possibilities of multiple campaigns. So it's actually many-to-many between user and campaign. So I think I'm left to setting the CampaignId manually. I even tried creating a dynamic property called ActiveCampaign which always returns the current campaign but it's useless and is not forcing the CampaignId to be set.
  
  I welcome any other thoughts.
  

• Hello,
  
  I'm trying to use this approach to achieve a one-to-one association. In my case there's an association between a User and a Team, and I need a navigation property in each of them.
  
  I bumped into a problem when adding data.
  
  This is what the models look like:
  
  public class Team
  {
      public int ID { get; set; }
      public string Name { get; set; }
      public int OwnerID { get; set; }
      public virtual User Owner { get; set; }
  }
  public class User
  {
      public int ID { get; set; }
      public string UserName { get; set; }
      public int TeamID { get; set; }
      public virtual Team Team { get; set; }
  }
  
  I added these bits to the DBContext:
  
  modelBuilder.Entity<User>()
              .HasRequired(u => u.Team)
              .WithMany()
              .HasForeignKey(u => u.TeamID);
  modelBuilder.Entity<Team>()
              .HasRequired(t => t.Owner)
              .WithMany()
              .HasForeignKey(t => t.OwnerID)
              .WillCascadeOnDelete(false);
  
  And now when adding data like this:
  
  u = new User();
  u.UserName = "farinha";           
  t = new Team("Flour Power");
  u.Team = t;
  t.Owner = u;
  context.Users.Add(u);
  context.Teams.Add(t);
  context.SaveChanges();
  
  or even like this:
  
  u = new User();
  u.UserName = "farinha";
  u.Team = new Team("Flour Power");
  context.Users.Add(u);
  context.SaveChanges();
  
  I'm getting the following error:
  
  Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.
  
  Any idea of how to solve this? Or should I be adding the data in some different way?
  
  Thanks in advance
  
  

• @asrfarinha: Of course it won’t work. Think about it, you are trying to add a new user which has a team, and the team itself is also new and this new team has the very same user as its owner. When you invoke SaveChanges(), EF tries to add these two new objects, now if it tries to add the User first, then it would need a TeamId to send along with other values, if it tries to add the Team object first in order to obtain a TeamId, then it would need an OwnerId which has to be obtained from the User record. Therefore, having two foreign keys that referencing each other table’s primary keys created a mutual dependency that would stop you from inserting related records in one single unit of work (i.e. a call to SaveChanges method). Although there are ways to solve this issue and proceed with the inserts but you should be aware that you haven’t really created a one-to-one FK association here. In fact, you’ve created two unidirectional one-to-many associations between User and Team which are totally unrelated and have nothing to do with each other. Basically if you need to have a bidirectional one-to-one association between your entities, then you should consider creating a shared primary key association instead since like I explained in the post, one-to-one foreign key associations cannot be bidirectional. Hope this helps.
  

• Thanks for the reply. I believe I managed to solve this by making a few changes:
  http://stackoverflow.com/questions/5957515/entity-framework-saving-data-in-one-to-one-associations
  
  These one-to-one associations are somewhat weird and uncommon, and that's why I'm a bit lost as to how to implement this one. According to the sample of the model I described, do you reckon a shared primary key association would be a better option? Thanks.
  

• @asrfarinha: Like I said, there are ways to get over this exception and save the dependent objects like the one that has been proposed on Stackoverflow but the main problem in essence that you can’t have a bidirectional association when creating a one-to-one FK relationship is still out there. Unfortunately you don’t have much of a choice here, if you need a one-to-one bidirectional association then shared primary key is the only type that EF currently supports and it is your only option. That being said, if your domain model allows at least one of the navigation properties to be read-only then there is a way to make your one-to-one FK association bidirectional. The trick is to replace the missing navigation property with a custom query. For example, let’s assume that you need to read/write the Owner information of a Team object (hence the Owner navigation property) but on the User side you only need to read the Team information of a User object: public class Team{    public int ID { get; set; }    public string Name { get; set; }    public int OwnerID { get; set; }
      public virtual User Owner { get; set; }}
  public class User{           public int ID { get; set; }    public string UserName { get; set; }
      [NotMapped]    public Team Team { get; set; }}
  public class Context : DbContext{    public Context()    {        ((IObjectContextAdapter) this).ObjectContext.ObjectMaterialized += OnObjectMaterialized;    }
      public DbSet<Team> Teams { get; set; }    public DbSet<User> Users { get; set; }
      protected override void OnModelCreating(DbModelBuilder modelBuilder)    {        modelBuilder.Entity<Team>()                    .HasRequired(t => t.Owner)                    .WithMany()                    .HasForeignKey(t => t.OwnerID);                            }
      private void OnObjectMaterialized(object sender, ObjectMaterializedEventArgs e)    {        if (e.Entity is User)        {            User user = (User) e.Entity;            user.Team = this.Teams.Single(t => t.OwnerID == user.ID);        }    }}As you can see above, every time you retrieve a User, the Team property gets populated at the time of object materialization. Hope you find this useful.
  

• Uff... I'm getting even more confused as to what option should I follow.
  In practice, both the User and the Team entities will be created at the same time, never deleted, and a Team won't ever change user. There will be exactly one Team per User and exactly one User per team. I'll need to have a way to navigate to the Team from the User and vice-versa.
  
  I'm almost leaning towards having it all in the same table as the same entity, but it doesn't make a lot of sense from a OO point of view, if you're thinking about each object as a "real-world" Entity.
  And thinking forwards, maybe a User will be allowed to have more than one Team in the future (maybe). So having them in separate tables would be good if I ever decide to change.
  

• @asrfarinha: If that’s the case then don’t create a one-to-one shared primary key. Your best bet would be to create a one-to-one FK association and make it bidirectional like the way I showed above. The only change you need to do is to have a real navigation property on User object (User.Team) and make the Owner property on Team object to be a read only query based navigation property since you said a Team would never change an Owner. If in the future the requirement for having more than one Team for a User comes up, then all you need to do is to drop the unique constraint on OwnerID and change User.Team to be of type ICollection<User> and your database schema would perfectly support that.
  

• Great to see you finish this second series on the 4.1 release. Extremely helpful!
  
  I'm wondering how you might enforce a 1 to 0..1 relationship on the DB though? Consider a uni-directional relationship between Person and User, where a Person has public int? UserId and public User User (you cannot navigate from User to Person).
  
  EF maps this as a one-to-many, where technically in the DB a User can have 0..n People. In this case putting a unique index constraint on Person.UserId won't work, as the DB will throw a DbException as soon as you try and insert a second Person row with null UserId.
  
  Triggers? Or is there something easier?
  

• @Dan: Great question! First of all, I should mention that this is a limitation of SQL Server as according to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a Unique constraint should disallow duplicate non-NULL values, but allow multiple NULL values. In SQL Server however, a single NULL is allowed but multiple NULLs are not.
  There are a couple of workarounds to this problem but if you are using SQL Server 2008, then your best bet is to define a Unique Filtered Index based on a predicate that excludes NULLs.
  Consider the following as the object model for your scenario:
  
  
  
  public class User
  {
      public int UserId { get; set; }
      public string Username { get; set; }
  }
  
  public class Person
  {
      public int PersonId { get; set; }
      public string Name { get; set; }
      public int? UserId { get; set; }
  
  
      public User User { get; set; }
  }
  
  
  public class Context : DbContext
  {
      public DbSet<User> Users { get; set; }
      public DbSet<Person> Persons { get; set; }
  
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<Person>()
                      .HasOptional(p => p.User)
                      .WithMany()
                      .HasForeignKey(u => u.UserId);
      }
  }
  
  
  
  And here is how we can create a filtered index to enforce an optional one-to-one relationship between User and Person:
  
  
  
  protected override void Seed(Context context)
  {
      context.Database.ExecuteSqlCommand("CREATE UNIQUE NONCLUSTERED INDEX idx_UserId_NotNULL ON People(UserId) WHERE UserId IS NOT NULL");           
  }
  
  
  Now you can have as many Persons as you want with a NULL UserId while it still disallows Persons with a same UserId. Thanks for your comment and hope this helps :)
  
  
  
  

• @Fred: Those Stackoverflow threads discuss about shared primary key associations which I fully explained in the third part of this series. Like I described in that post, shared primary keys have a few limitations (like difficulty in saving related objects or the impossibility for having multiple dependent navigation properties) which don’t make them uncommon but relatively rare and in many schemas, a one-to-one association is represented with a foreign key field and a unique constraint which is the motivation behind the mapping described in this post. Please take a look at the part 3 of this series if you haven’t already. Hope this series help you choose the best mapping type for your one to one relationship scenarios :)
  

• OK I see now. The the "shared" 1:1 scenario is where you have PK of one table related to PK of another table.
  
  This scenario is also 1:1 but PK of one table relates to FK of another table. And the approach is to model n:1 and then convert it to 1:1 in the db, using a unique constraint.
  
  This is what I don't get: I can model it as 1:1 and it still works for me? Unless I am doing something wrong. I can do this:
  
  1) .HasRequired(q => q.SomeEntity)
  .WithRequiredPrincipal() m.MapKey("SomeOtherEntityID"))
  .WillCascadeOnDelete(true);
  2) set unique constraint in db
  
  So I model a 1:1, and I exactly that, and I also get cascade deletes without using triggers. I trust your code, so I don't understand why mine is working? I am using EF4.1.
  

• @Fred: Your fluent API code precisely creates an independent one-to-one association. In EF we usually create a 1:1 association by making the PK of the dependent entity to be also a FK of the principal entity, something we called a shared primary key association which you saw in the third part of this series. But let’s say you don’t like this and want to create a 1:1 association on a column other than the PK (and yet want to keep the association as a 1:1). EF would let you do that with one restriction which is you can't expose the FK property in your object model and that’s exactly what you did with your fluent API code. This of course works, but you have to be aware of 2 caveats: 1. If you look at your database you’ll see that while your object model showing a 1:1 association, the resulting relationship on the database is not, it’s a typical 1:* relationship and if you reverse engineer the generated database into an EDMX file, you’ll see that the relationship will be even picked up as a 1:* association by EF! Long story short, you still have to create a unique constraint on the FK (SomeOtherEntityID in your example) yourself, if you want to ensure your database consistency. 2. Like I mentioned, your code essentially creates an independent association as opposed to the FK association I have created in this article. Independent associations are the only type of relationship available in the first release of the EF and have been obsoleted ever since FK associations introduced in EF 4.0. The reason for that is because changing relationships between entities and concurrency checks was really difficult especially in N-Tier application scenarios when using independent associations. Therefore, the recommendation is to use this new foreign key association feature whenever possible. If you are using your fluent API code to have a 1:1 bidirectional association, as that’s the only benefit I can see with your mapping, then have a look at my answer to Asrfarinha above where I show a trick to make a one-to-one FK association bidirectional. Hope this helps.
  

• All of this is confusing. I guess its because EF doesnt really support 1:1 FK relations.
  
  In my scenario i have a main class that has many related 1:1 classes.
  
  A 1-1 B
  A 1-1 C
  A 1-1 D
  C 1-* E
  
  what i need is to be able to have A.B .. and B.A .. so that i can do B.A.C.SomeProp += someval etc.
  
  
  At this point, i'm not 100% sure which direction i should take.
  
  Oh, and i'm working off an existing database and getting relations done properly is a nightmare when it should be simple. What i don't understand is why FK isnt enough. (I'm also thinking of using lists and having a property that takes the first element of the list for the many side which i want as a 1 side)
  
  There also seems to be some importance into how the entities are created
  
  A = new A() { B = new B() }
  context.AList.Add(A)
  may work but
  B = new B() { A = new A() }
  context.BList.Add(B)
  could fail.
  
  So there seems to be a logical way to set members that makes sense depending on how the relations are set which is not explained anywhere. At least i havent found somewhere explaining it.
  
  Is this something you plan on explaining at some point ?
  

• @Michel C: You can use the trick I showed in this post to create 1:1 FK relationships between the entities in your model, hence avoiding “collection type navigation properties treated like a single object“.
  
  
  About saving a graph of related objects, it doesn’t really matter which object you pick to save your graph with, as long as the relations are set properly so I don’t see any reason why context.BList.Add(B) could ever fails. If you got an exception while trying to save the objects then can you please be more specific?
  

• This helped me tackle the fluent api configuration for my model. Thank you very much!
  

• Thanks and "Damet Garm" ;)
  

• You are always doing optional your foreign keys via fluent api but why don't you mention that we can also achieve it via Nullable types. I feel that it is much better to make it nullable because model define itself more well without any configuration. Is there any downside to prefer nullable type for making foreign key optional ?
  

• Yakup you want to read Fred's question and Morteza's answer. This 2 posts only could make another blog serie. Morteza thank you for the effort.
  
  

• Awesome post. Thanks a ton!!!
  

• I found this post very useful in understanding one-to-one relationships in EF CF. I too have an instance where Class X has two properties of type Class Y and I have followed the example here closely. At first, it all seemed to work - I get my not null on the columns, I get the correct Ids and these are correctly set. However, when performing an integration test to check that both properties have been set, I leave one as null and expect it to throw an exception. But it doesn't. Instead, it copies the values and id of the not-null version. So I end up with, for instance, the billing address and the delivery address the same if one is null which is not the intended behaviour.
  Has anyone else noticed this behaviour at all?
  Regards