EF4 TPT with abstract classes - defining relationships

I've been going around in circles with this and don't seem to be able to google the right answers - even after hours spent on this, so you're my last resort!

Setup

In my web app I would like to enable users to use different authentication mechanisms to access their accounts. In addition to the usual user/password thing I'd like to enable them to use Google's OpenId, Yahoo's OpenId, or even Facebook. This seems rather straightforward to map into classes: an abstract Account with several <something>Account classes inheriting some basic properties from Account. I started with the following two classes:

public abstract class Account
{
    public int Id { get; set; }
    public int OwnerId { get; set; }

    public virtual Person Owner { get; set; }
}

public class OpenIdAccount : Account
{
    public string Identifier { get; set; }
}

Being a bit of a perfectionist, and doing a lot of db dev in my day job, I decided table per type (TPT) would be the most desirable option. As EF4 uses TPH by default, on the DbContext side of things I defined:

public class MySampleDb : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Account> Accounts { get; set; }
    public DbSet<OpenIdAccount> OpenIdAccounts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>().MapHierarchy(a => new
        {
            a.Id, 
            a.OwnerId
        }).ToTable("Account");

        modelBuilder.Entity<OpenIdAccount>().MapHierarchy(oid => new
        {
            oid.Id,
            oid.Identifier
        }).ToTable("OpenIdAccount");
    }
}

Now, I wished to start with some simple tests, and thought that seeding the database on each run was a good way to have consistent data to work with:

protected override void Seed(MySampleDb context)
{
    Person johns = new Person
    {
        Id = 1,
        Nickname = "John Skeet"
    };

    OpenIdAccount google = new OpenIdAccount
    {
        Id = 2,
        OwnerId = 1,
        Identifier = "https://www.google.com/accounts/o8/id?id=AItOawnmUz4e6QIn9mgd98WMAbnzC25sji5lpSM"
    };

    context.People.Add(johns);
    context.Accounts.Add(google);
    context.SaveChanges();
}

(Not sure why but DbContext didn't seem to ever try populate my db with the above data until I exlicitly called .SaveChanges()... any ideas?)

The Problems

One

In the database, EF didn't define any relationships between Account and OpenIdAccount. This was the first warning sign something was not right; surely OpenIdAccount should have its Id defined as both a PK and a FK pointing at Account.Id?

Two

I get the following UpdateException when .net tries to execute .SaveChanges():

A value shared across entities or associations is generated in more than one location. Check that mapping does not split an EntityKey to multiple store-generated columns.

Next steps

Today is my first day with EF4 and code first development. Having spent numerous hours reading about EF4 + code-first + custom mapping I came to a point where I'm permanently stuck and need a kick in the right direction before I can get going again :-)

So I'm hoping that you guys can make sense of the above and explain some silly mistake / misunderstanding on my part!

Solution

No worries, you are in right place :) Let's get into your questions:

(Not sure why but DbContext didn't seem to ever try populate my db with the above data until I exlicitly called .SaveChanges()... any ideas?)

That's exactly how it designed to work. In the Seed method, you would have to call SaveChanges after you add the new objects to their respective DbSets. So you are good in there.

In the database, EF didn't define any relationships between Account and OpenIdAccount.

Your inheritance implementation is Table per Concrete Type or TPC inheritance and NOT TPT and it coming from the fact that you make your Account class to be abstract and what you see in terms of not having a one to one relationship between Account and OpenIdAccount is the exact default behavior of EF when it comes to TPC mapping. If you remove the abstract keyword from the Account class, then you would have a TPT and your code will work just fine.

So does that mean you should give up with your TPC and turn it to be a TPT? Well, that's of course one solution but you don't have to go for it if you still like to keep your TPC since it is absolutely possible to have TPC with Code First and we just need to make some slight changes to your model to make it work.

Solution:

TPC means “Create a completely separate table for each non-abstract type in my hierarchy”. Note that because there is no foreign key between the two tables we need to take care of providing unique keys, therefore we have to switch off identity on the primary key property. There are 2 ways for that:

1. By using DataAnnotations from System.ComponentModel.DataAnnotations:

public abstract class Account 
{
    [StoreGenerated(StoreGeneratedPattern.None)]
    public int Id { get; set; }
    public int OwnerId { get; set; }
    public virtual Person Owner { get; set; }
}

2. By using FluentAPI:

modelBuilder.Entity<Account>().Property(a => a.Id)
    .StoreGeneratedPattern = System.Data.Metadata.Edm.StoreGeneratedPattern.None;

After you switch it off through one of the above ways, you would see that the exception you are getting goes away and the model starts working.

Also to make it really TPC, you should map everything in each table because in TPC there is a table for each class, and each of those tables has a column for every property of that type:

modelBuilder.Entity<Account>().MapHierarchy(a => new {
    a.Id,
    a.OwnerId,
})
.ToTable("Accounts");

modelBuilder.Entity<OpenIdAccount>().MapHierarchy(o => new {
    o.Id,
    o.OwnerId,
    o.Identifier
})
.ToTable("OpenIdAccounts");

All that being said, I think TPC is not meant to use in this scenario and you should use TPT. For a more detailed discussion on this topic, you can check out this excellent post by Alex James:
How to choose an Inheritance Strategy