Using Entity Framework Code First and ASP.NET Membership Together

Some time ago I was involved as a software designer and developer in an MVC 3 project that used Entity Framework Code First 4.1 in a repository layer. During development we postponed dealing with security as requirements were pretty simple (simple logons with a single Administrators role plus the requirement that users should only be able to see their own data). When we were close to deployment, we ran the aspnet_regsql tool against the database that EF had created for us to create the SQL schema and data that the Application Services such as Membership and Roles require. We also added an additional userName parameter to a Get method in the repository to filter records for the currently logged in user. Finally, we added Authorize attributes to a number of controllers to make sure they are only accessible for users that are logged in. We then deployed the application and database, created a number of users using the MvcMembership section we had added to the site, and announced the presence of the site. All of this worked great, but we ran into issues when started work on the next version of the application.

Dealing with Database Initializers

To make it easy during development to keep the database in sync with the Code First model we were using, we used the DropCreateDatabaseIfModelChanges strategy. With this strategy, the database gets dropped and recreated whenever the underlying model changes (e.g. you change the properties of an entity, change mappings, add a new entity and so on). You can create a class that inherits this base class and then override the Seed method to add some default records that you want available when the database is recreated. Here's a simple example that comes with this article's download.

public class MyDropCreateDatabaseIfModelChanges : DropCreateDatabaseIfModelChanges<PeopleContext>
{
  protected override void Seed(PeopleContext context)
  {
    context.People.Add(new Person 
        { FirstName = "Imar", LastName = "Spaanjaars", DateOfBirth = new DateTime(1971, 8, 9) });
    context.People.Add(new Person 
        { FirstName = "John", LastName = "Doe", DateOfBirth = new DateTime(1950, 1, 1) });
  }
}

This code simply creates two new Person instances, and adds them to the People collection. EF then takes care of persisting these objects in the database.

To make the Entity Framework aware of this class you have a few options. One option is to set a key called DatabaseInitializerForType in the appSettings section of web.config as follows:

<add key="DatabaseInitializerForType Namespace.To.Your.ObjectContext, AssemblyNameForThisType" 
     value="Namespace.To.Your.DropCreateDatabaseIfModelChangesClass, AssemblyNameForThisType" />

As an alternative you can call Database.SetInitializer and pass in an instance of your DropCreateDatabaseIfModelChangesClass class. I have implemented this strategy in the sample application. I wrapped this call in a static method in a separate class as follows:

public class PeopleContextInitializer
{
  public static void Init()
  {
    Database.SetInitializer(new MyDropCreateDatabaseIfModelChanges()); 
  }
} 

I then call the Init method from the Application_Start method in Global.asax:

protected void Application_Start()
{
  PeopleContextInitializer.Init();

  ...
}

Whenever the application starts, the correct initializer is fed to the Entity Framework which then uses it whenever it needs to recreate the database. This in turns causes the Seed method to fire, inserting the default records that the application requires.

However, since we added security to the application and blocked all controllers except the Login controller for anonymous users, we found that we could no longer log in after the database was recreated. Once the database is recreated, the required schema for the application services such as Membership and Roles is gone. What we wanted was a correctly configured Membership and Roles database, along with a default user and role. This would enable us to log in and perform tasks only available for logged in users. The first step to solving this issue was to programmatically add a user and role to the database. This is easy enough with code like this in Application_Start in the Global.asax:

const string password = "Testing123";
const string userName = "Administrator";
const string roleName = "Administrators";

if (!Roles.RoleExists(roleName))
{
  Roles.CreateRole(roleName);
}
if (Membership.GetUser(userName) == null)
{
  Membership.CreateUser(userName, defaultPassword);
  Roles.AddUserToRole(userName, roleName);
}

While this code runs fine against a database that has been prepared for the application services, it will crash when the required objects are not present in the database. When you use the membership services at the frontend (for example, when using the Login or CreateUserWizard controls in a Web Forms application), the database is created for you. However, when using the API to programmatically add users and roles, you need to make sure your database already has the database schema required by the application services. Fortunately, again this is pretty easy to do. In the sample application you find code that looks like this:

using System;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web.Management;

namespace EFCodeFirstAndMembership.EF
{
  public static class ApplicationServices
  {
    readonly static string defaultConnectionString = 
          WebConfigurationManager.AppSettings["DefaultConnectionString"];
    readonly static string connectionString = 
          WebConfigurationManager.ConnectionStrings[defaultConnectionString].ConnectionString;
    readonly static SqlConnectionStringBuilder myBuilder = 
          new SqlConnectionStringBuilder(connectionString);

    public static void InstallServices(SqlFeatures sqlFeatures)
    {
      SqlServices.Install(myBuilder.InitialCatalog, sqlFeatures, connectionString);
    }

    public static void UninstallServices(SqlFeatures sqlFeatures)
    {
      SqlServices.Uninstall(myBuilder.InitialCatalog, sqlFeatures, connectionString);
    }
  }
}

This code gets the database connection for the application by retrieving its name from an appSetting called DefaultConnectionString. It then uses the security API to create the requested application services.

I can now easily create the schema with a single line of code from the Seed method in the DropCreateDatabaseIfModelChanges class as follows:

ApplicationServices.InstallServices(SqlFeatures.Membership | SqlFeatures.RoleManager); 

Now whenever the application starts up. the Init method is called which tells EF which database initialization strategy to use. When EF detects the model has changed it recreates the database and then calls Seed. The Seed method in turn creates the database schema for the application services. So, by the time the Init method in Global.asax is done, the database is ready and the code that inserts users and roles can successfully run. Or so I thought...

It turns out that Entity Framework doesn't create the database right away when you set the initializer. Instead, it uses a lazy loading strategy to postpone creating the model until the first time it's used. This posed a big problem for us. Each page in the web site is protected, so we needed the Membership login functionality before EF is ever used. In addition, the code that creates the user and the role also needs a properly set up database.

Fortunately, this is also easy to fix. You can force EF to create the database by calling Database.Initialize on an instance of your object context. In the sample application you find the code that's responsible for this in the PeopleContextInitializer class which looks like this:

public class PeopleContextInitializer
{
  public static void Init()
  {
    Database.SetInitializer(new MyDropCreateDatabaseIfModelChanges());   
    using (var db = new PeopleContext())
    {
      db.Database.Initialize(false);
    }    
  }
}

When Initialize is called, the database is created immediately. The parameter of the Initialize method can be used to force EF to recreate the model even if it had been created before. I have set it to false, as I only need it to run once. If you want a fresh database on each application start, you can set it to true instead.

The call to Initialize was the final piece of the puzzle. Now whenever my model changes, EF recreates the database, some default records are inserted, the database schema for the application services is set up correctly and a user and a role is created so I can log in immediately to the site.

Note that (some of) this may no longer be necessary when EF Code First migrations matures and becomes usable in production scenarios.

Downloads

You can download the full sample code for the demo application discussed in this article. Note: this application is used only to demonstrate these concepts. It does not demonstrate best practices for ASP.NET MVC or Entity Framework.

Download the full code (C# only)


Where to Next?

Wonder where to go next? You can read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Wednesday, August 24, 2011 3:50:50 AM Tom Kamphuis said:
Nice post! I'm definitely gonna use this in some of my latest projects. Thanks for sharing this!
On Thursday, September 29, 2011 7:04:25 AM Viktor said:
Interesting article. I'm not sure whether application services belong in a domain model. POCOs that we get from EF4.1 code first are great, but as soon as I start mixing that with application services, everything gets a bit messy.
On Thursday, September 29, 2011 7:18:12 AM Imar Spaanjaars said:
Hi Viktor,

This is mostly used to solve issues during development time. I wouldn't have this code in production, so personally I don't mind that I am mixing POCOs and App services. But if you do, you could also call this from GLobal.asax.

Cheers,

Imar
On Sunday, October 16, 2011 1:18:26 PM webrus said:
Nice article merging EF and Asp.net membership . how about a article using Entity Framework and Sql filestream varbinary(max) to create a PhotoAlbum using geography data type to geo tag the photos. Do you also have  a book on this.  Thanks in advance
On Sunday, October 16, 2011 1:26:25 PM Imar Spaanjaars said:
Hi webrus,

I don't have any articles or books on this subject, and nothing planned yet either. Sorry.... ;-)

Cheers,

Imar
On Sunday, November 06, 2011 3:14:14 PM Scott said:
Thanks. This works great for me. One question though. Do you know of a way where you can code EF classes for the membership tables so EF LINQ queries can use them with the non-membership tables?
On Monday, November 07, 2011 6:51:52 AM Imar Spaanjaars said:
Hi there,

You can create the aspnet_* tables to your EF diagram after they have been created.

Cheers,

Imar
On Saturday, December 24, 2011 4:27:51 AM jhalbrecht said:
Thanks for the post. Clear and easy to grok. Implemented most of your solution in my application. I implemented my db seed/init in web.config.

Thanks! - jeffa
On Thursday, January 05, 2012 4:36:40 PM Tahir Hassan said:
for me, calling "Initialize" did not create the database.  However the method "CreateIfNotExists" (found on "Database" too).
On Thursday, January 05, 2012 5:23:34 PM Imar Spaanjaars said:
Hi Tahir,

Are you sure you're using a class that inherits DropCreateDatabaseIfModelChanges? That works fine for me. Does the sample application work for you?

Cheers,

Imar
On Monday, January 09, 2012 1:15:06 AM czetsuya said:
Hi,

Thanks for a nice article. I was able to create the aspnet tables using the script you have provided. But I just have some questions:

1.) First I need to manually create the classes for aspnet tables right? Then map then like this:
public DbSet&lt;AspnetApplication> AspnetApplications { get; set; }

2.) For the many-to-many relationship aspnet_Users-aspnet_Roles, I've override OnModelCreating:
modelBuilder.Entity&lt;AspnetUser>()
                .HasMany(u => u.AspnetRoles)
                .WithMany(r => r.AspnetUsers)
                .Map(m =>
                    {
                        m.ToTable("aspnet_UsersInRoles");
                        m.MapLeftKey("UserId");
                        m.MapRightKey("RoleId");
                    });
My question is how do I initialize value for table aspnet_UsersInRoles in the override Seed method?

Thanks,
czetsuya
On Monday, January 09, 2012 10:02:27 AM Imar Spaanjaars said:
Hi there,

That's what this article is about:

Roles.AddUserToRole(userName, roleName);

adds records to that table.

Cheers,

Imar
On Wednesday, March 14, 2012 6:21:49 AM Luke said:
Thanks for the article!
Would you happen to  know how to create the membership tables in code for SQL CE perhaps by using the ASP.NET Universal Providers.

Cheers
Luke
On Wednesday, March 14, 2012 9:49:55 AM Imar Spaanjaars said:
Hi Luke,

Nope, sorry, don't have a solution available. Maybe the Universal Providers have an API available as well?

Imar
On Tuesday, September 25, 2012 3:02:59 PM Willy said:
Good article!
On Saturday, June 15, 2013 4:03:04 PM Fábio said:
Thanks for this post!
I'm trying to use EF5 Code First with ASP.NET Membership, but when I configure Migrations, always causing errors, and I solved my problem with this article.

Thanks so much and sorry for my poor english! kkkkk :)

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.