Unable to generate explicit migration (EF5) (migrations pending)

Question

We are using Code-first migrations with EF5 on (localdb)\v11.0 (Vstudio 2012) and everything has worked nicely so far.

However - today I needed to create a couple of indexes on a few of tables and ran into problems.

First I did this in PM:

PM> add-migration AddIdxToOutage
Scaffolding migration 'AddIdxToOutage'.

I modified the code in the scaffolded migration to:

public override void Up()
        {
            Sql(@"CREATE NONCLUSTERED INDEX [idx_WtgId_StartDateTime_EndDateTime] ON [dbo].[Outages]
(
    [WtgId] ASC,
    [StartDateTime] ASC,
    [EndDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
        }

I updated the database and the result was this:

PM> update-database -startupprojectname D3A.Data -force -verbose
Using StartUp project 'D3A.Data'.
Using NuGet project 'D3A.Data'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'D3A.Data.StorageContext' (DataSource: (localdb)\v11.0, Provider: System.Data.SqlClient, Origin: Convention).
Applying code-based migrations: [201310301258520_AddIdxToOutage].
Applying code-based migration: 201310301258520_AddIdxToOutage.
CREATE NONCLUSTERED INDEX [idx_WtgId_StartDateTime_EndDateTime] ON [dbo].[Outages]
(
    [WtgId] ASC,
    [StartDateTime] ASC,
    [EndDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
[Inserting migration history record]
Running Seed method.

The idx was created on the table and everybody was happy.

However - when I created the next index, I ran into problems. I created an empty migration task as

PM> add-migration AddIdxToState
Unable to generate an explicit migration because the following explicit migrations are pending: [201310301258520_AddIdxToOutage]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

Pardon my French - but WT*?

There seems to be no way around this. I can revert to the migration step preceding addition of the first idx, add the idx'es again and the same thing happens again.

Can you tell me what I am missing here? What am I doing wrong?

Edit:

I initially thought that my trouble was executing raw SQL against the database/localdb, but it seems like everything I do now stops after the first add-migration.

I just added a new table to the database and this is the std-out result from PM Console:

PM> add-migration AddMyLoggerTable
Scaffolding migration 'AddMyLoggerTable'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration 201310301419063_AddMyLoggerTable' again.
PM> update-database -startupproject DongEnergy.D3A.Data -verbose
Using StartUp project 'D3A.Data'.
Using NuGet project 'D3A.Data'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'D3A.Data.StorageContext' (DataSource: (localdb)\v11.0, Provider: System.Data.SqlClient, Origin: Convention).
Applying code-based migrations: [201310301419063_AddMyLoggerTable].
Applying code-based migration: 201310301419063_AddMyLoggerTable.
CREATE TABLE [dbo].[MyLoggerTable] (
    [id] [int] NOT NULL,
    [functionId] [int],
    [elapsedTime] [bigint],
    [noOfRecords] [int],
    [dateCreated] [datetime] DEFAULT getDate()
)
[Inserting migration history record]
Running Seed method.
PM> add-migration AddMyLoggerTableFunction
Unable to generate an explicit migration because the following explicit migrations are pending: [201310301419063_AddMyLoggerTable]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

Notice how I add a new, empty migration task, use the CreateTable-method and have that successfully updated in the database. But when I add a new migration step, it complains that the task that was just "committed" to the database is still "pending" - even though both migrationhistory and database objects have been updated.

Answer 1

This might not solve the OP question, but I had a similar issue when I was developing a new database and tried to add two migrations before doing any update to the database.

My solution was to run my application so the pending migration(s) could update the database. The application updates the database automatically with the help of a MigrateDatabaseToLatestVersion. This can be accomplished by typing Update-Database from the Package Manager Console as well.

If a migration is added and more changes are made which should be added to the same migration, there's also no need to remove it and re-add it (which I tried to do at first). You can just update the existing pending migration, the help is displayed when running the Add-Migration tool from the Package Manager Console (emphasis by me).

The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration [Timestamp]_MigrationName' again.

In other words, run the Add-Migration tool with the same Id as the pending operation, and the new changes will be merged with the old. If you notice that the changes are not merged, you can use the -F flag (as in Add-Migration <Name> -Force) to force the migration, as anthony-arnold notes in the comments.