EF 4.1 code-first: How to design and map these entities?

Question

I have 3 entities: Member, AuthenticationToken, and Email.

1. Each Member may has many AuthenticationTokens
2. Each AuthenticationToken may has one or zero Email
3. Each Member may has zero or one PrimaryEmail (from Emails table). Really the PrimaryEmail is one of the AuthenticationTokens's associated Email

So I have:

public class Member {
    public int MemberId { get; set; }
    public int? PrimaryEmailId { get; set; }
    public virtual Email PrimaryEmail { get; set; }
    public virtual ICollection<AuthenticationToken> AuthenticationTokens { get; set; }
}

public class AuthenticationToken {
    public int AuthenticationTokenId { get; set; }
    public int MemberId { get; set; }
    public virtual Member Member { get; set; }
    public virtual Email Email { get; set; }
}

public class Email {
    public int EmailId { get; set; } // is same as AuthenticationTokenId that the email associated with it
}

With design I explained above, I can add Member and AuthenticationToken, but when I want to attach a Email to a Member or AuthenticationToken (or both) I give this error:

The INSERT statement conflicted with the FOREIGN KEY constraint etc.

Is this design correct??? How can I design my tables (and entities) to achieve my purpose? And how can I map my entities in code-first? Have you any idea please?

Answer 1

I personally use the fluent API in EF 4.1 to configure all of my entities when I don't feel the default conventions will understand me, so I will answer using the fluent API.

Here is how I would set up the models:

public class Member
{
    public Member()
    {
        AuthenticationTokens = new List<AuthenticationToken>();
    }

    public int MemberId { get; set; }

    public virtual Email PrimaryEmail { get; set; }
    public virtual ICollection<AuthenticationToken> AuthenticationTokens { get; set; }
}

public class AuthenticationToken
{
    public int AuthenticationTokenId { get; set; }

    public virtual Email Email { get; set; }
}

public class Email
{
    public int EmailId { get; set; }
}

And this is my context and fluent configuration:

public class ExampleApplicationContext : DbContext
{
    public ExampleApplicationContext()
        : base("ExampleApplicationConnection")
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // No cascade on delete because the primary email may be held by an authentication token.
        modelBuilder.Entity<Member>()
            .HasOptional(x => x.PrimaryEmail)
            .WithOptionalDependent()
            .Map(x =>
            {
                x.MapKey("EmailId");
            })
            .WillCascadeOnDelete(false);

        // Cascade on delete because an authentication token not associated with a Member makes no sense.
        modelBuilder.Entity<Member>()
            .HasMany(x => x.AuthenticationTokens)
            .WithRequired()
            .Map(x =>
            {
                x.MapKey("MemberId");
            })
            .WillCascadeOnDelete();

        // No cascade on delete because an email may be held by a Member.
        modelBuilder.Entity<AuthenticationToken>()
            .HasOptional(x => x.Email)
            .WithOptionalDependent()
            .Map(x =>
            {
                x.MapKey("EmailId");
            })
            .WillCascadeOnDelete(false);
    }

    public DbSet<Member> Members { get; set; }
}

I will do my best here to explain my reasoning as to why I designed it like this. First of all, it appears that in your model Member should be the root aggregate (the boss of the other entities). What I mean is an Authentication Token makes no sense unless it belongs to a specific Member. An Email also makes no sense alone unless it either belongs to a Member or belongs to an AuthenticationToken. For this reason AuthenticationToken does not have a property to find out what Member it is attached to (to find this out you first need a Member and than just look at its collection). Essentially, everything revolves around a Member object. Without a Member an AuthenticationToken cannot be created. And without a Member or an AuthenticationToken an Email cannot be created.

I'm not entirely sure how comfortable you are with the fluent API in EF 4.1, so if you have any questions leave a comment and I will do my best to answer them. I have also included a small sample application that I used to build and verify the model I presented above. If you want to run the program (it is a small Console app) you just have to modify the connection string in App.config to point to your instance of SQL Server.

One thing that concerns me is the fact that Email can belong to both a Member and an AuthenticationToken. My concern comes from the fact that I had to setup some interesting cascade deletes. I don't know all of your requirements, however, and this setup appears to work just fine so that may not be an issue.

Example Console Application