Custom Initialization Strategy for EF Code First that doesn't drop tables to add a column

Question

The latest EF Code First NuGet package comes with a custom implementation of IDatabaseInitializer called DontDropDbJustCreateTablesIfModelChanged. As the name implies, when a model change is detected, it will not drop and recreate the whole database, just drop and recreate the tables.

Say I have this model class:

public class User
{
    public string Username { get; set; }

    // This property is new; the model has changed!
    public string OpenID { get; set; }
}

How would one go about implementing an IDatabaseInitializer that doesn't drop any tables either. In this case, it would just add an OpenID column to the User table?

Answer 1

I think it is a matter of SQL. So for SQL Server you can write something like:

public class MyInitializer : IDatabaseInitializer<MyContext>
{
    public void InitializeDatabase(MyContext context)
    {
        context.Database.SqlCommand(
            @"
            IF NOT EXISTS (SELECT 1 FROM sys.columns AS col
                           INNER JOIN sys.tables AS tab ON tab.object_Id = col.object_Id
                           WHERE tab.Name = 'User' AND col.Name = 'OpenId')
            BEGIN
                ALTER TABLE dbo.User ADD OpenId INT; 
            END");
    }
}

But in the same way you can execute such script without adding it to your application which I think is much better approach.

Answer 2

With the current version of Code First, you cannot simply amend your schema and preserve any data that you might have in your tables. If maintaining data, such as reference data / lookup tables is important with this release you can create your own Initializer and override the Seed method to populate your tables

public class MyDbInitializer : DropCreateDatabaseIfModelChanges<MyContext>
{
    protected override void Seed(MyContext context)
    {
        var countries = new List<Country>
        {
            new Country {Id=1, Name="United Kingdom"},
            new Country{Id=2, Name="Ireland"}
        };

        countries.ForEach(c => context.Countries.Add(c));
    }
}

And then use this in your Application_Start:

Database.SetInitializer<MyContext>(new MyDbInitializer());

I believe that this is being addressed currently by the EF Team, but wasn't ready for release at the time the Code First drop came out. You can see a preview here: Code First Migrations