CODEWITHSUNDEEP
c#entity-framework.net-coreentity-framework-migrations
nathanjw
nathanjw

Reputation: 1062

Updating a table with a new column using EF Core 2.0 Migrations

I'm new to EF Core and have been working through an online course and a book. I created my database context and used dotnet ef migrations add init followed by dotnet ef database update and all was well much to my delight. I added a new column to my model and created and applied a new migration but was met with:

> Applying migration '20180417175820_AddOsTypeColumn'.
Failed executing DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [MachineTypes] (
    [MachineTypeID] int NOT NULL IDENTITY,
    [Description] varchar(15) NULL,
    CONSTRAINT [PK_MachineTypes] PRIMARY KEY ([MachineTypeID])
);
System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'MachineTypes' in the database.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInA
ction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrap
CloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectio
nLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, B
ulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean as
yncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, In
t32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbComma
ndMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection,
 IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadO
nlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migration
Commands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String c
ontextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:09aa3af2-6ce8-4331-a586-c79841f4e1b6
Error Number:2714,State:6,Class:16
There is already an object named 'MachineTypes' in the database.

I looked at this post Adding new column using update database in entity framework core which had the same initial problem as mine but the only solution provided didn't really fit my situation; my log indicated that it was applying the correct migration but was then trying to create an existing table as if it was running the init migration.

As it was only a practice/learning database I dropped it from SQL Server and started again. This time, following the steps described above worked as expected. The only difference this time that I was aware of was that I wasn't connected to the database when adding the new column/new migration. Would that have been the cause?

Upvotes: 1

Views: 3181

Answers (1)

Fred Kleuver
Fred Kleuver

Reputation: 8047

The only difference this time that I was aware of was that I wasn't connected to the database when adding the new column/new migration. Would that have been the cause?

I think you're mixing up terms here. Adding a new column is an operation on the database; adding a new migration is an operation on your project.

Updating a database without being connected to a database is not possible, so I'm assuming you're talking about changing your model / adding a migration. EF does not look at any database for this, so no.

To clarify (assuming code-first approach)

When you create a migration (add migration), EF will look at these two things:

  • Your DbContext class, to determine the full schema that your database needs to have
  • Your migration classes, to determine the (delta) schema that needs to be applied to your database (and thus the code to be generated for the new migration)

When you apply a migration (update database), EF will look at these two things:

  • The migration history table of your existing database (if it exists and has one), to determine which migrations have already been applied
  • Your migration classes, to see if there are any migrations that have not been applied yet

So when you get the error you posted, it means either:

  • Your second migration was created without EF taking into account your existing migrations (you changed namespaces in between, pointed to the wrong migrations folder, etc)
    • new migration includes changes from old migrations
  • You called update on a database that already had one or more migrations applied to it, but did not have the (correct) migration history
    • same migration gets applied multiple times

Upvotes: 1

Related Questions