Option 1:1 mapping, main entity doesn't have FK in it

Question

I am using entity framework 4, mvc4 and code first.

I'm struggling with creating an option 1:1 mapping, where the main entity that has the optional 1:1 mapping doesn't have the FK in it:

public class User
{
   [Column("user_id")]
   public int Id {get;set;}

   public virtual House House {get;set;}  // optional mapping
}

public class House
{
   [Column("house_id")]
   public int Id {get;set;}

   [Column("user_id")]
   public int UserId {get;set;}
}

Notice how the user table doesn't have teh houseId column.

How can I map this correctly?

Note: the below method isn't what i really want to do since it forces me to add a navigational property on the House model also back to User.

I tried this method, although I had to add a virtual property to the House model which I didn't want to do: How do I code an optional one-to-one relationship in EF 4.1 code first with lazy loading and the same primary key on both tables?

So my configuration looks like with the above attempt:

public class UserConfiguration : EntityTypeConfiguration<User>
        {
            public UserConfiguration()
            {
                this.ToTable("User", SchemaName);
                this.HasKey(x => x.Id);
                this.HasOptional(x => x.House);
            }
        }

    public class HouseConfiguration : EntityTypeConfiguration<House>
    {
        public HouseConfiguration()
        {
            this.ToTable("House", SchemaName);
            this.HasKey(x => x.Id);
            this.HasRequired(vc => vc.User).WithRequiredDependent(v => v.House);
        }
    }

But when I do this, saving the model I get this error:

Cannot insert explicit value for identity column in table 'House' when IDENTITY_INSERT is set to OFF

Note: without the above setup (mapping and configuration), the House entity saves just fine to the database and the identity is setup correctly.

score 1 · Answer 1 · answered May 15 '13 at 16:46

Remove the UserId property from House, remove the this.HasRequired... mapping from the HouseConfiguration constructor and use in UserConfiguration:

this.HasOptional(x => x.House).WithRequired();

This will define a shared primary key association (i.e. House.Id is primary key of House and the foreign key to User at the same time).

If you have an existing database with a separate foreign key column user_id in the House table and this column has a unique key constraint to enforce a one-to-one relation in the database you cannot map this as one-to-one relationship with Entity Framework because EF doesn't support foreign key one-to-one associations.

You would have to map this as one-to-many relationship in this case and unfortunately you can't have a single reference House on the principal User. You would have to use a collection of Housees instead (and ensure in business logic that you never add more than one element to this collection, otherwise upon saving you would get an exception due to a violation of the unique FK constraint in the House table) or no navigation property at all in User. But then you need a navigation reference User in entity House so that EF would be able to map a relationship at all (at least a navigation property on one side of a relationship is always needed).

score 0 · Answer 2 · answered May 15 '13 at 16:27

0

Can't rollback to EF4, but was only using it in a similar way a while ago, so don't believe this has changed.

You need to set a Key on the House object and set it to DB generated:

using System.ComponentModel.DataAnnotations.Schema
using System.ComponentModel.DataAnnotations
...

public class House
{
        [Column("house_id")]
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Column("user_id")]
        public int UserId { get; set; }
}

answered May 15 '13 at 16:27

Ben Shearer

291
2
5

Now i get: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'House_Id'. – loyalflow May 15 '13 at 16:32
1:1 relationships need the dependent to have same primary key. DatabaseGeneratedOption.None on the dependent primary Key. Once you Tell EF that you have a 1:1 relationship, it is expecting the same key. Even though DB first that isnt required. But do you really have a 1:1 relationship ? – phil soady May 15 '13 at 16:38
@soadyp is it an optional 1:1, meaning the User account can have 0 or 1 House records. – loyalflow May 15 '13 at 17:59

Option 1:1 mapping, main entity doesn't have FK in it

2 Answers2