Including Asp.net Simple Membership Tables as Part of Your Entity Framework Model

Simple Membership is a membership system originally created for Web Matrix but is now being used by some of the Asp.Net project templates. When using Asp.Net along with the Entity Framework, it would be very nice if you could access the database tables created for SimpleMembership just like you would any of the other entity tables you use in your project. By default, SimpleMembership will check to see if they tables required to run SimpleMembership exist, if not it will create them. This post will describe how to create these tables yourself using the Entity Framework and Migrations.

Tools Used

• Visual Studio 2012
• Asp.Net MVC 4 (although SimpleMembership also works outside of MVC)
• Entity Framework 5 (with migrations)
• SQL Server (although you could use any data store supported by the Entity Framework)

For this demo, I am going to start off by creating a new Asp.Net MVC 4 Internet Application Project

After the project is created, you will notice a file called AccountModels.cs under the Models folder in your web project. This is where our work begins. There are 4 tables required by SimpleMembership:

• webpages_Membership - Contains all the basic information about membership such as UserId and Password
• webpages_OAuthMembership – Enables support for OAUTH by linking providers with a given user
• webpages_Roles – Enables support for basic roles
• webpages_UsersInRoles – links users with their given roles

If you would like to see these tables created automatically by SimpleMembership, you can simply run your web project (F5) and create a new user. This will trigger SimpleMembership to create these four tables for you. But that is not what we want here. We want to create them ourselves.

Migrations

Entity Framework Migrations allow you to manage the schema of your database using a C# DSL. To use Migrations in a project, you have to first enable them. To do this, open up the package manager console in Visual Studio and type:

>Enable-Migrations

This will add a new Migrations folder to your project with some boiler plate configuration. Before we add the SimpleMembership entities to our DbContext, we should create an initial migration which will represent the initial schema of our database (which just includes the UserProfiles table). Again in the package manager console, type:

>Add-Migration Initial

This will create a migration named ‘Initial’ which represents the current schema of our database. Now we can update the database to reflect this schema:

>Update-Database

Now our database should look like this:

Entity classes

Now it is time to create the entity classes to represent the tables we need for SimpleMembership. First, the webpages_Membership table:

[Table("webpages_Membership")]
public class Membership
{
    public Membership()
    {
        Roles = new List<Role>();
        OAuthMemberships = new List<OAuthMembership>();
    }

    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UserId { get; set; }
    public DateTime? CreateDate { get; set; }
    [StringLength(128)]
    public string ConfirmationToken { get; set; }
    public bool? IsConfirmed { get; set; }
    public DateTime? LastPasswordFailureDate { get; set; }
    public int PasswordFailuresSinceLastSuccess { get; set; }
    [Required, StringLength(128)]
    public string Password { get; set; }
    public DateTime? PasswordChangedDate { get; set; }
    [Required, StringLength(128)]
    public string PasswordSalt { get; set; }
    [StringLength(128)]
    public string PasswordVerificationToken { get; set; }
    public DateTime? PasswordVerificationTokenExpirationDate { get; set; }

    public ICollection<Role> Roles { get; set; }

    [ForeignKey("UserId")]
    public ICollection<OAuthMembership> OAuthMemberships { get; set; }
}

This is just a POCO class decorated with some attributes to ensure we get the correct database schema. You can ignore the OAuthMembership and Role classes for now (we will create those next). The most confusing part of this class is probably:

[Key, DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
public int UserId { get; set; }

The Key attribute tells the Entity Framework that this property represents the primary key of this entity. The DatabaseGenerated attribute is needed because the SimpleMembership insists on being able to insert this value itself (rather than have the database generate it).

Now the code to represent the webpages_OAuthMembership table:

[Table("webpages_OAuthMembership")]
public class OAuthMembership
{
    [Key, Column(Order = 0), StringLength(30)]
    public string Provider { get; set; }

    [Key, Column(Order = 1), StringLength(100)]
    public string ProviderUserId { get; set; }

    public int UserId { get; set; }

    [Column("UserId"), InverseProperty("OAuthMemberships")]
    public Membership User { get; set; }
}

In this table, the primary key consists of two fields: Provider and ProviderUserId. Notice that both of these fields have the Key attribute as well as the Column attribute which is required by the Entity Framework for composite keys.

Lastly, here is the code to represent the webpages_Roles table:

[Table("webpages_Roles")]
public class Role
{
    public Role()
    {
        Members = new List<Membership>();
    }

    [Key]
    public int RoleId { get; set; }
    [StringLength(256)]
    public string RoleName { get; set; }

    public ICollection<Membership> Members { get; set; }
}

Relationships

One thing you may have noticed in the code for the entities above is the Column, InverseProperty, and ForeignKey attributes on some of the relationship/navigation properties. For example:

[Column("UserId"), InverseProperty("OAuthMemberships")]
public Membership User { get; set; }

These are used to ensure that the database field names used in the relationships will exactly match those of the expected SimpleMembership fields. In the example above, it will use the field "UserId" instead of something like "User_UserId".

Another thing you may have noticed is that we do not create an explicit entity to represent the webpages_UsersInRoles table. Instead, we will create this table in the OnModelCreating method of our UsersContext. So here is all the code we need to add to our UsersContext class:

public DbSet Membership { get; set; } public DbSet Roles { get; set; } public DbSet OAuthMembership { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Membership>()
        .HasMany<Role>(r => r.Roles)
        .WithMany(u => u.Members)
        .Map(m =>
        {
            m.ToTable("webpages_UsersInRoles");
            m.MapLeftKey("UserId");
            m.MapRightKey("RoleId");
        });
}

This adds DbSet<> properties for the three entities we created and also specifies the many to many relationship between Memberships and Roles. That is all of the code needed to fully represent the SimpleMembership tables. Now we can create a new database migration (you must build your project first):

>Create-Migration MembershipTables

And then update the database (After building your project again):

>Update-Database

Now your database schema should look like this:

And you can now access the SimpleMembership tables in your code using the EntityFramework:

using(var db = new UsersContext())
{
    var memberships = db.Membership
        .Include("Roles")
        .Include("OAuthMembership")
        .ToList();
    foreach(var member in memberships)
    {
        member.IsConfirmed = true;
    }

    db.SaveChanges();
}

So why would you ever want to do this? I’ll tackle that issue in a future post. Hope this helps.

You can download a sample project here which contains all of the code above.