EF 4.1 Code First - Mapping a one to one relationship with non standard column names

Question

I know a lot of people is asking about one-to-one relationships on EF 4.1 but I can't seem to find an answer for this problem.

I have these POCO classes:

public class Contact
{
    public decimal nCont_id { get; set; }

    public virtual WebsiteMembership WebsiteMembership { get; set; }
    //a bunch of other properties
}

public class WebsiteMembership
{
    public int WebsiteMembershipId { get; set; }

    public virtual Contact Contact { get; set; }
}

Website membership's primary key (WebsiteMembershipId) is also a foreign key that references Contact's nCont_id.

I'm trying to set this up using the fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //This is being done because of the unconventional column names I have to live with
    modelBuilder.Conventions.Remove<NavigationPropertyNameForeignKeyDiscoveryConvention>();

    //bunch of other stuff
    modelBuilder.Entity<Contact>().ToTable("contacts");
    modelBuilder.Entity<Contact>().HasKey(b => b.nCont_id);
    modelBuilder.Entity<Contact>().HasOptional(b => b.WebsiteMembership).WithRequired(b => b.Contact).Map(b => b.MapKey("WebsiteMembershipId"));

    modelBuilder.Entity<WebsiteMembership>().ToTable("WebsiteMembership");
    modelBuilder.Entity<WebsiteMembership>().HasKey(b => b.WebsiteMembershipId);

}

But I get an exception when I try doing:

var websiteMembership = context.WebsiteMemberships.Where(c => c.WebsiteMembershipId == 1645942).FirstOrDefault();

Exception:

Schema specified is not valid. Errors: (263,6) : error 0019: Each property name in a type must be unique. Property name 'WebsiteMembershipId' was already defined.

Does this mean I can't set the same primary key as a foreign key on EF CodeFirst? Or I'm doing something wrong here? Would it work if the primary key wasn't the same column as the foreign key?

Advice is very much appreciated!

Salesmanship

Answer 1

I think when you define this mapping...

modelBuilder.Entity<Contact>()
    .HasOptional(b => b.WebsiteMembership)
    .WithRequired(b => b.Contact)
    .Map(b => b.MapKey("WebsiteMembershipId"));

...you implicitely define what's the principal and what's the dependent of the relationship. Obviously Contact is the principal because having the WebsiteMembership property set is optional for the Contact, it can be stored without the WebsiteMembership. The WebsiteMembership entity on the other side has a required reference to a Contact which means it depends on the Contact.

The principal (Contact) is the entity with the primary key, the dependent (WebsiteMembership) the entity with the foreign key. So, when you use MapKey to set the name WebsiteMembershipId of the foreign key column you define a column for the table WebsiteMembership, not for Contact. But WebsiteMembership already has a property called WebsiteMembershipId. (I believe that's the point where EF complains that a "Property name 'WebsiteMembershipId' was already defined.) In a one-to-many relationship the foreign key would be the ContactId as a separate property/column and you would have to use HasForeignKey instead of MapKey. But in a one-to-one relationship it is clear that the foreign key is the primary key at the same time, so you don't need to define the foreign key at all.

To cut a long story short: Just remove MapKey:

modelBuilder.Entity<Contact>()
    .HasOptional(b => b.WebsiteMembership)
    .WithRequired(b => b.Contact);

(But now I am curious if you can define a relationship between an int and a decimal property or if you get the next error.)

Affairs

Excellent explanation! Thank you very much everything worked just fine. I've tried the above piece of code before, however because my field was int and not decimal EF was looking for a field Contact_nCont_id. I then used your piece of code and made the property decimal and everything worked beautifully. – Salesmanship