Update-Database command is not working in ASP.Net Core / Entity Framework Core because object in database already exists

Question

I was updating my database by the command line, but then I manually updated one of my tables.

This seems to have disrupted my ability to update-database. I receive the following error when I try to update:

 System.Data.SqlClient.SqlException: There is already an object named 'ClientsAndTestimonials' in the database.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection) 
    at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection) 
    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) 
    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) 
    at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType) 
    at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.<>c__DisplayClass0_0.<Configure>b__0() 
    at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
    at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)
 ClientConnectionId:d89989a8-ce8b-4167-be7e-fcddc4bcdf98
 Error Number:2714,State:6,Class:16
 There is already an object named 'ClientsAndTestimonials' in the database. 

I have been trying to fix this problem for the past few days. Most fellow developers suggest some variation of using Add-migration "Reset" -IgnoreChanges, like John Salewski from the following link.

However, I keep getting an error that says "A parameter cannot be found that matches parameter name 'IgnoreChanges'".

Any suggestions would be greatly appreciated!

Solution 1

There is no -IgnoreChanges currently in EF Core (see here) but you can achieve the equivalent by commenting out all the code in the Up() method and applying the migration. This will take a snapshot of the current model state so that subsequent migrations will only include changes from that point forward.

So if you just made some incremental model change and you don't have this initial baseline you may need to remove those changes, apply the baseline migration, then add your changes back and add a 2nd migration.

Solution 2

The answer: DONT USE Update-Database AND Database.EnsureCreated() AT SAME TIME.

In EF 6, this problem is solved by using the -IgnoreChanges flag. This is described here.

There is no -IgnoreChanges flag in EF Core 5, and there are two migration scenarios in EF Core 5.

SCENARIO 1.

At an early stage of development, we can use a pair of methods: A Database.EnsureDeleted() and the Database.EnsureCreated().

So we should use these methods in the context constructor, for example:

public MyContext(DbContextOptions<MyContext> options) : base (options)
{
    Database.EnsureDeleted();
    Database.EnsureCreated();
}

The EnsureDeleted() method will delete our database. The EnsureCreated() method will recreate our database. All data stored in the database will be lost. Note that EnsureDeleted () and EnsureCreated() will only work if our data model is changed.

This scenario is appropriate at an early stage of development, when we can allow the DB to be recreated on each running application after changing our data model.

SCENARIO 2.

We can also use the manual update of the database. To do this, we need to use Add-Migration and Update-Database in the package manager.

After adding the migration, the EF tool creates a new migration class with the methods void Up (MigrationBuilder migrationBuilder) and void Down (MigrationBuilder migrationBuilder).

After that, we should use Update-Database, which allow us to change our database.

When we use Add-Migration-Update-Database and EnsureDeleted()-EnsureCreated() at the same time we get "there is already an object named 'Entity name' in the database. " error when updating the database step. As Steve Green said, we can delete all the generated code using the Up and Down methods. And it will be useful, but we have to do it after all the changes in our data model

Solution 3

Use Alter column instead AddColumn in up() function

    migrationBuilder.AlterColumn<bool>(
        name: "IsActive",
        table: "Advertisements",
        nullable: false,
        defaultValue: true);

Or

      public override void Up()
      {
        AddColumn("dbo.Products", "Description", c => c.String(maxLength: 50));
      }

asp.net migration

You can remark some parts in up function if you dont want to full effect to database.

If your migration command angried for making those. Reset and rebase migration. How: Delete migration content both from visualstudio and sql and add-migration again and update-database

Comments

[SS_DBA]

Try -Ignore_Changes

[johnny 5]

do you have any Data in the DB that you need, if not just drop the whole db and re add it

[Kelsey Steele]

@WEI_DBA, that didn't work, I got the same error. Any other ideas?

[Kelsey Steele]

@johnny5 I do have a lot of Data in the DB. Is there anything else I can do, that lets me keep the data?

[johnny 5]

@KelseySteele I believe Steve Greene's answer should work

[Dinch]

My case, error message was right, my connection string was pointing wrong data source. So, check your conn string again. Hope saves some time for someone else.

[Kelsey Steele]

I am still getting the same error. I'm not sure if my sequence is correct. The Up() method is within the migration. So I have to first create a new migration to see the Up() method, but I think this is creating the snapshot or at least I think so because it's called ApplicationDbContextModelSnapshot. I can't see how I can comment out the Up() method without creating a snapshot that includes subsequent migrations. Should I delete all of my previous migrations?

[Steve Greene]

OK, didn't realize you had prior migrations. So it sounds like you added ClientsAndTestimonials to your database manually instead of letting migrations create it, so now when you add that to your models EF tries to add it again. Same concept applies - look at your Up() code and comment out the lines that create objects that already exist. You can always reset migrations as long as all the deployed databases are up to that point.

[Kelsey Steele]

Thank you, Steve! That seemed to do the trick. I followed the directions from your link to reset migrations and now I can update-database. I really appreciate your help.

[ManirajSS]

should i comment my very first (items inside) up & down migration in order to have base model snapshot?

[Steve Greene]

Yes. The model stored in resource field in code is the key thing. It is used to do the next compare (the database record is not). Plus, if you start a new database it will still create all your objects because it would be comparing an empty model to the stored model.

[S.Serpooshan]

see also this: Update existing database with Entity Framework Code First in MVC