Trying out EF code-first and migrations - stumbling blocks

Question

I've always been a database oriented programmer, so up to this day, I've always used a database-driven approach to programming and I feel pretty confident in T-SQL and SQL Server.

I'm trying to wrap my head around the Entity Framework 6 code-first approach - and frankly - I'm struggling.

I have an existing database - so I did a Add New Item > ADO.NET Entity Data Model > Code-First from Database and I get a bunch of C# classes representing my existing database. So far so good.

What I'm trying to do now is explore how to handle ongoing database upgrades - both in schema as well as "static" (pre-populated) lookup data. My first gripe is that the entities that were reverse-engineered from the database are being configured with the Fluent API, while it seems more natural to me to create the new tables I want to have created as a C# class with data annotations. Is there any problems / issues with "mixing" those two approaches? Or could I tell the reverse-engineering step to just use data annotation attributes instead of the Fluent API altogether?

My second and even bigger gripe: I'm trying to create nice and small migrations - one each for each set of features I'm trying to add (e.g. a new table, a new index, a few new columns etc.) - but it seems I cannot have more than a single "pending" migration...... when I have one, and I modify my model classes further, and I try to get a second migration using add-migration (name of migration), I'm greeted with:

Unable to generate an explicit migration because the following explicit migrations are pending: [201510061539107_CreateTableMdsForecast]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

Seriously ?!?!? I cannot have more than one, single pending migration?? I need to run update-database after every single tiny migration I'm adding?

Seems like a rather BIG drawback! I'd much rather create my 10, 20 small, compact, easy-to-understand migrations, and then apply them all in one swoop - no way to do this!?!? This is really hard to believe..... any way around this??

Answer 1

Is there any problems / issues with "mixing" those two approaches?

1. No, there is no problem to mix them.
2. You can do more with fluent config than with data annotations.
3. Fluent config overrides data annotation when constructing the migration script.
4. You can use data annotations to generate DTOs and front-end/UI constraints dynamically - saves a lot of code.
5. Fluent API has class EntityTypeConfiguration which allows you to make domains (in DDD sense) of objects dynamically and store them - speeds up work with DbContext a lot.

I cannot have more than a single "pending" migration

1. Not 100% true. ( Maybe 50% but this is not a showstopper )
2. Yes, the DbMigrator compares your model "hash" to the database model "hash" when it generates the Db - so it blocks you before you make your new small migration. But this is not a reason to think you can not make small migration steps. I do only small migration steps all the time.
3. When you develop an app and you use your local db you apply the small migrations one by one as you develop functionality - gradually. At the end you deploy to staging/production all your small migrations in one dll with all the new functionality - and they are applied one by one.

Answer 2

It is true that you can only have one pending migration open at a time during development. To understand why, you have to understand how the migrations are generated. The generator works by comparing the current state of your database (the schema) with the current state of your model code. It then effectively creates a "script" (a C# class) which changes the schema of the database to match the model. You would not want to have more than one of these pending at the same time or else the scripts would conflict with each other. Let's take an simple example:

Let's say I have a class Widget:

class Widget
{
    public int Id { get; set; }
    public string Name { get; set; }
}

and a matching table Widgets in the database:

Widgets
-------
Id (int, PK, not null)
Name (nvarchar(100), not null)

Now I decide to add a new property Size to my class.

class Widget
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Size { get; set; }  // added
}

When I create my migration, the generator looks at my model, compares it with the database and sees that my Widget model now has a Size property while the corresponding table does not have a Size column. So the resulting migration ends up looking like this:

public partial class AddSizeToWidget : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Widgets", "Size", c => c.Int());
    }

    public override void Down()
    {
        DropColumn("dbo.Widgets", "Size");
    }
}

Now, imagine that it is allowed to create a second migration while the first is still pending. I haven't yet run the Update-Database command, so my baseline database schema is still the same. Now I decide to add another property Color to Widget.

When I create a migration for this change, the generator compares my model to the current state of the database and sees that I have added two columns. So it creates the corresponding script:

public partial class AddColorToWidget : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Widgets", "Size", c => c.Int());
        AddColumn("dbo.Widgets", "Color", c => c.Int());
    }
    ...
}

So now I have two pending migrations, and both of them are going to try to add a Size column to the database when they are ultimately run. Clearly, that is not going to work. So that is why there is only one pending migration allowed to be open at a time.

So, the general workflow during development is:

1. Change your model
2. Generate a migration
3. Update the database to establish a new baseline
4. Repeat

If you make a mistake, you can roll back the database to a previous migration using the –TargetMigration parameter of the Update-Database command, then delete the errant migration(s) from your project and generate a new one. (You can use this as a way to combine several small migrations into a larger chunk if you really want to, although I find in practice it is not worth the effort).

Update-Database –TargetMigration PreviousMigrationName

Now, when it comes time to update a production database, you do not have to manually apply each migration one at a time. That is the beauty of migrations -- they are applied automatically whenever you run your updated code against the database. During initialization, EF looks at the target database and checks the migration level (this is stored in the special __MigrationHistory table which was created when you enabled migrations on the database). For any migration in your code which has not yet been applied, it runs them all in order for you, to bring the database up to date.

Hope this helps clear things up.