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!!

ocuenca · Accepted Answer · 2015-02-20 03:11:32Z

2

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.

edited Feb 20, 2015 at 3:11

answered Feb 19, 2015 at 23:01

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

littlemoli Over a year ago

Thanks for re-formatting my post. Maybe its confusing when I said the database exists. The database was created by EF, and and there is a __MigrationHistory table in the db. There is no schema, no data model change neither. Nothing need to be migrated. The issue is: When .Include("Courses"); is called, exception was thrown: System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Courses' in the database.

ocuenca Over a year ago

Hello @littlemoli , the problem is that exception is throwed when the DB already exist. Anyway try to run the steps that I recommend you to stablish an initial point

Pang · Accepted Answer · 2017-03-22 01:11:03Z

0

It would probably work if you do a trick.

Step 1: Using Package Manager Console to run Update_Database with temporary empty database.

Step 2: Try to get table __HistoryMigration from the temporary database to create and copy to table in your existing database.

edited Mar 22, 2017 at 1:11

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Mar 22, 2017 at 1:00

Liam LY

1

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related