How to avoid recreate an existing database using automatic Code First Migration

Question

I am using EF6, and having an issue with database automatic migration. The database exists, and there is no schema nor data changes in the db. But for some strange reason, the application seems to trying to re-create an existing table in the db on machine reboot. And therefore cause errors. My questions are:

Why there will be automatic db migration when there is no db schema change? What triggered it?
How to fix it.

I use Include method to load entities like this, and the error occurs when I call it:

using (var context = new MyDataContext())
{
    return context.Students.Include("Courses");
}

In the Student class I have this navigation property:

public class Student
{    
...

   public virtual ICollection<Course> Courses { get; set; } 
}

In the Configuration.cs:

public Configuration()
{
   this.AutomaticMigrationsEnabled = true;
   this.AutomaticMigrationDataLossAllowed = true;
}
...

I have also set the automatic database migration in the MyDataContext class like this:

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDataContext, MyProject.DataModel.Migrations.Configuration>());

Currently, the issue is, 99% of the time, everything works just fine, and for some unknown reason, the following error occurs:

System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Courses' in the database. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.b__2e() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, VersionedModel sourceModel, VersionedModel targetModel, Boolean downgrading) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.b__b() at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.MigrateDatabaseToLatestVersion2.InitializeDatabase(TContext context) at System.Data.Entity.Internal.InternalContext.<>c__DisplayClassf1.b__e() at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() at System.Data.Entity.Internal.LazyInternalContext.b__4(InternalContext c) at System.Data.Entity.Internal.RetryAction1.PerformAction(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action1 action) at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.Include(String path) at System.Data.Entity.Infrastructure.DbQuery`1.Include(String path)

Any helps are appreciated! Thank you!!

octavioccl octavioccl · Accepted Answer · 2015-02-19T23:01:01

If you are working with an existing database, I suggest you check this page:Code First Migrations with an existing database. There you are going to see it's necessary to do these two steps first before making any changes to your model:

Run the Add-Migration InitialCreate –IgnoreChanges command in Package Manager Console. This creates an empty migration with the current model as a snapshot.
Run the Update-Database command in Package Manager Console. This will apply the InitialCreate migration to the database. Since the actual migration doesn’t contain any changes, it will simply add a row to the __MigrationHistory table indicating that this migration has already been applied.

When you run the app using automatic migrations, if it’s doing any type of data initialization, it will check the __MigrationHistory table to see if the database needs to be updated.If there’s no __MigrationHistory table (which there won’t be in an existing database), the model won’t be verified against the database with automatic migrations. So you need to get that table into your existing database.To do that you need to follow the steps that I describe above. When you run Update-Database command it will execute the Up method in the initial migration, which has no effect on the database. And it will create the __MigrationHistory table in your database based on the current state of the model. That way, if you are using automatic migrations, next time Code First does db initialization, it will compare the current model to the one stored in the __MigrationHistory to determine if the database needs to be migrated/updated.

How to avoid recreate an existing database using automatic Code First Migration

2 Answers