I am using Microsoft’s Entity Framework 4.1’s Code First approach to rapidly develop a basic MVC 3 application for a friend of mine. The app is also leveraging the standard MVC Internet Website template and its build-in membership management functionality. Hence it uses the SQL Membership Provider which adds a bunch of tables to the database by means of the aspnet_regsql.exe utility. Thus not all database access is via the EF.

To get it all working together, my initial, naive approach was to use the utility to generate the SQL script needed to build out the database and then execute that script as part of the Seed() method of my database initializer. (I was subclassing both DropCreateDatabaseIfModelChanges for my website and DropCreateDatabaseAlways for my automated tests.)

The method to actually execute the script looked like this:

public static void ExecuteSqlSript(Database database, string scriptPath)
{
    var conn = database.Connection;
    if (conn.State == ConnectionState.Closed)
        conn.Open();
 
    var fullScript = File.ReadAllText(
        scriptPath);
 
    foreach (var command in Regex.Split(fullScript, @"\bGO\b"))
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = command;
        try
        {
            cmd.ExecuteNonQuery();
 
        }
        catch (Exception e)
        {
            throw new ApplicationException(e.Message + 
                "\r\n\r\nCommand: " + command, e);
        }
    }
}

This worked great for my first few iterations, until I needed to add a model to the framework to represent some of the data on the aspnet_User table.

Mapping my new User entity to the existing table name was easy. It just required adding the following to my DbContext implementation:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<User>()
        .ToTable("aspnet_Users");
    modelBuilder.Entity<User>()
        .Property(u => u.Name).HasColumnName("UserName");
}

This lead to the aspnet_regsql generated script failing because EF now was creating the aspNet_User table before the script ran. So my next step was to hack the script by adding the following just before the code to create the table:

IF EXISTS (SELECT name
                FROM sysobjects
                WHERE (name = N'aspnet_Users')
                  AND (TYPE = 'U'))
   AND NOT EXISTS(SELECT * FROM sys.COLUMNS 
                  WHERE Name = N'ApplicationId'  
                    AND Object_ID = Object_ID(N'aspnet_Users'))
BEGIN
	--This indicates the presense of a the table 
        --as created by the Entity Framework Code First
	--My EF schema does no use/create the full table 
        --so just drop it so that the script below will do its job.
    DROP TABLE [dbo].aspnet_Users
END
 
IF (NOT EXISTS (SELECT name
                FROM sysobjects
                WHERE (name = N'aspnet_Users')
                  AND (TYPE = 'U')))
BEGIN
  PRINT 'Creating the aspnet_Users table...'
/* Actual CREATE TABLE statement omitted for brevity...

This was a hack, but this was rapid development of the “just get it working” sort. And it worked great, until I added a new model that referenced a user, like so:

public class Record
{
    public int RecordId { get; set; }
 
    public Guid UserId { get; set; }
 
    [Required]
    [ForeignKey("UserId")]
    public virtual User Recorder { get; set; }
 
    [MaxLength(4000)]
    public string Notes { get; set; }
}

This busted my hack because I could no longer just drop the table. There was now a foreign key constraint that had to be deleted first. While I could certainly patch my hack to drop the key too, with more foreign keys on the horizon, it was obvious that this approach was going to quickly become unmanageable.

The problem was that, especially for my automated tests, I wanted to drop and recreate the database regularly. The right solution, therefore was to somehow execute the aspnet_regsql script after EF created the database itself, but before EF built out any tables. If I could do that, EF was smart enough to just use the existing aspnet_User table.

In my search for a solution, I found this article which described how to create a database initializer that would just drop all the table and recreate them w/o dropping and recreating the database itself. This seems promising but its approach to dropping all the tables didn’t account for foreign keys or any other objects that might need dropped too. Modifying it to do so might be possible but could also become a real maintenance headache. My concern was how complex the code would become to find and drop all dependencies which my code might create against the aspnet tables w/o dropping those that the aspnet_regsql script itself created. It might be easy; it might not. I still felt that simply dropping the whole database and rebuilding it was the best approach. So I set out to find a way to inject some functionality between EF’s database creation and schema build out actions. I posted on the EF forum and got a suggestion to try migrations. That wasn’t a bad idea, but still seemed more complicated than I wanted.

Going back to the blog on custom initialization strategies, I looked harder at the provided code, and googled around for some additional examples of custom strategies and in the end was able to come up with a custom initializer that did what I wanted. It

  1. drops the database (conditionally or always, base on a constructor arg).
  2. creates the database itself.
  3. runs the aspnet_regsql script.
  4. uses EF to build out the remaining schema.

Without further adue, here is the code:

using System;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Transactions;
 
public class CreateDatabaseWithAspNetRegSql<TContext> 
    : IDatabaseInitializer<TContext>
    where TContext : DbContext
{
    public enum CreationStrategy { AlwaysCreate, CreateIfModelChanged }
 
    private readonly CreationStrategy _creationStrategy;
 
    public CreateDatabaseWithAspNetRegSql(
        CreationStrategy creationStrategy)
    {
        _creationStrategy = creationStrategy;
    }
 
    #region IDatabaseInitializer<Context> Members
 
    public void InitializeDatabase(TContext context)
    {
        bool dbExists;
        using (new TransactionScope(TransactionScopeOption.Suppress))
        {
            dbExists = context.Database.Exists();
        }
        if (dbExists)
        {
            if (_creationStrategy == CreationStrategy.CreateIfModelChanged
                && context.Database.CompatibleWithModel(false)) 
                return;
 
            context.Database.Delete();
        }
 
        CreateDatabase(context);
 
        DbInitializer.DoAspNetRegSql(context.Database);
 
        CreateTablesForModels(context);
 
        Seed(context);
        context.SaveChanges();
    }
 
    #endregion
 
    #region Private/Protected Methods
 
    private static void CreateDatabase(TContext context)
    {
        var masterDbConnString = context.Database
            .Connection.ConnectionString
            .Replace(context.Database.Connection.Database, "master");
 
        //TODO: Find way to create db in an agnostic way.
        using (var conn = new SqlConnection(masterDbConnString))
        {
            conn.Open();
 
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = string.Format("CREATE DATABASE [{0}]", 
                    context.Database.Connection.Database);
                cmd.ExecuteNonQuery();
            }
        }
    }
 
    private static void DoAspNetRegSql(Database database)
    {
        //TODO: This file name reference is a hack, 
        //need a better way of handling this!
        ExecuteSqlSript(database, @"C:\Users\Ken\Documents\Visual Studio 2010\Projects\MVCSandbox\_Resources\aspnet_regsql.sql");
    }
 
    protected static void ExecuteSqlSript
        (Database database, string scriptPath)
    {
        var conn = database.Connection;
        if (conn.State == ConnectionState.Closed)
            conn.Open();
 
        var fullScript = File.ReadAllText(
            scriptPath);
 
        foreach (var command in Regex.Split(fullScript, @"\bGO\b"))
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = command;
            try
            {
                cmd.ExecuteNonQuery();
 
            }
            catch (Exception e)
            {
                throw new ApplicationException(e.Message + 
                    "\r\n\r\nCommand: " + command, e);
            }
        }
    }
 
    private static void CreateTablesForModels(TContext context)
    {
        var modelBuildoutScript = ((IObjectContextAdapter)context)
            .ObjectContext.CreateDatabaseScript();
 
        RemoveTableCreationCommandsForTablesCreatedByAspNetRegSql(ref modelBuildoutScript);
 
        context.Database.ExecuteSqlCommand(modelBuildoutScript);
    }
 
    private static readonly Regex __aspNetCreateTableCommandFinder 
        = new Regex(@"create table \[dbo\]\.\[aspnet_\w+\][^;]*;");
 
    private static void RemoveTableCreationCommandsForTablesCreatedByAspNetRegSql
        (ref string script)
    {
        script = __aspNetCreateTableCommandFinder.Replace(script, string.Empty);
    }
 
    #endregion
 
    #region Public Methods
 
    protected virtual void Seed(TContext context)
    {
 
    }
 
    #endregion
}