How to design an optimal Emergency Contact pattern to work with Membership?

Question

I have to store some emergency information to a membership and I struggle with how to design the SQL tables. I found this very useful old question but I’m still struggling with the answer.

I noticed that an emergency contact will have the same information as a standard user. Then I would like to reuse the same structure as I already have to do so. Let’s me show you what I have so far (I use code first, C# and I simplified the model for demonstration purpose):

Membership table

public class Membership
{
    public string Id { get; set; }
    public tring PhoneNumber { get; set; }
    public tring UserName { get; set; }
    public tring Email { get; set; }
    public bool IsDeactivated { get; set; }
    public bool IsAccountClosed { get; set; }
    /// <summary>
    /// In some cases like teammate, a membership can be linked to another membership. We'll be able to build teammate and other scenario.
    /// </summary>
    public string MembershipGroupId { get; set; }
    public int DisplayOrder { get; set; }
    public virtual UserProfile UserProfile { get; set; }
    /// <summary>
    /// The most of the time, a membership will have some emergency contacts
    /// </summary>
    public virtual ICollection<EmergencyContact> EmergencyContacts { get; set; }
}
}

If an emergency contact can be linked to many membership and a membership can have many emergency contact, then I should create a many to many relationships.

EmergencyContact

public class EmergencyContact : Entity
    {
        public string MembershipId { get; set; }
        public string MembershipEmergencyId { get; set; }
        public virtual Membership Membership { get; set; }
        public virtual Membership MembershipEmergency { get; set; }
    }

And I defined the FluentAPI like below:

modelBuilder.Entity<EmergencyContact>()
                .HasKey(x => new { x.MembershipId, x.MembershipEmergencyId });


// EmergencyMembership to Membership
            modelBuilder.Entity<EmergencyContact>()
                .HasRequired(x => x.Membership)
                .WithMany(x => x.EmergencyContacts)
                .HasForeignKey(x => x.MembershipId);

            // EmergencyMembership to MembershipEmergencyId
            modelBuilder.Entity<EmergencyContact>()
                .HasRequired(x => x.MembershipEmergency)
                .WithMany(x => x.EmergencyContacts)
                .HasForeignKey(x => x.MembershipEmergencyId);

And finally, I got this error Schema specified is not valid. Errors: The relationship EmergencyContact_Membership' was not loaded because the type Membership' is not available.

As you can see, EmergencyContact refers to membership twice because I expect that an emergency contact will have a standard membership as any other users. FluentAPI seams to not like that, I need some help to make this to work.

Thank you very much,

David

FYI - Membership is very old and not very easy to customize. If you have a choice, it would be better to use [ASP.NET Identity](http://www.asp.net/mvc/overview/security). It is much easier to add profile information and to configure DI with the ASP.NET Identity framework. — NightOwl888, Mar 22 '16 at 03:39
@NightOwl888 Indeed, membership here is ASP.NET Identity. I gave that name to recognize easily information between membership information (Password, LastLoginDate, etc.) and user profile information (first & last name, address, etc.). Everything works with DI too, I use Unity to be precised. So I just need to design correctly EmergencyContacts :) — David Létourneau, Mar 22 '16 at 10:47
My question looks like a self-referencing many to many. I'll investigate on it... http://stackoverflow.com/questions/5125052/self-referencing-many-to-many-recursive-relationship-code-first-entity-framework — David Létourneau, Mar 22 '16 at 11:43

score 0 · Accepted Answer · answered Mar 23 '16 at 14:58

I got the answer after many search! What I wanted to do is called “self-referencing many to many”. In my application, I expect to use the same structure as I already have (Membership and UserProfile tables) to store the emergency contacts relationships. In order to do so, if you look at my question, the EmergencyContact table was good and the ICollection in the Membership table too.

The key thing is with Fluent API. The way of defining a self-referencing many to many is like this:

modelBuilder.Entity<EmergencyContact>()
                .HasKey(x => new { x.MembershipId, x.MembershipEmergencyId });

//
            // For intance, David's membership can have a Joseph, Andrée and René membership as emergency contacts 
            // and those one can also be used as emergency contacts by Adeline's membership.
            //
            // A membership can have many emergencycontact
            modelBuilder.Entity<Membership>()
                .HasMany(x => x.EmergencyContacts)
                .WithRequired(x => x.Membership)
                .HasForeignKey(x => x.MembershipId)
                .WillCascadeOnDelete();

            // An emergencycontact can have many membership.
            modelBuilder.Entity<EmergencyContact>()
                .HasRequired(x => x.MembershipEmergency)
                .WithMany()
                .HasForeignKey(x => x.MembershipEmergencyId)
                .WillCascadeOnDelete(false);

Hope this could help someone else,

David

How to design an optimal Emergency Contact pattern to work with Membership?

1 Answers1