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!

do you have any Data in the DB that you need, if not just drop the whole db and re add it — johnny 5, Apr 28 '17 at 19:30
@WEI_DBA, that didn't work, I got the same error. Any other ideas? — Kelsey Steele, Apr 28 '17 at 19:31
@johnny5 I do have a lot of Data in the DB. Is there anything else I can do, that lets me keep the data? — Kelsey Steele, Apr 28 '17 at 19:34
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. — Dinch, Oct 28 '19 at 17:31

Steve Greene · Accepted Answer · 2021-07-12T17:51:27.980

41

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.

edited Jul 12 '21 at 17:51

answered Apr 28 '17 at 19:31

Steve Greene

12,029
1
33
54

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? – Kelsey Steele Apr 28 '17 at 20:01
4

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](https://weblog.west-wind.com/posts/2016/jan/13/resetting-entity-framework-migrations-to-a-clean-slate) as long as all the deployed databases are up to that point. – Steve Greene Apr 29 '17 at 13:58
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. – Kelsey Steele May 01 '17 at 15:29
should i comment my very first (items inside) up & down migration in order to have base model snapshot? – ManirajSS Sep 28 '17 at 10:50
1

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. – Steve Greene Sep 28 '17 at 14:00
see also this: [Update existing database with Entity Framework Code First in MVC](https://stackoverflow.com/a/28550030/2803565) – S.Serpooshan Aug 26 '18 at 05:05

score 7 · Answer 2 · answered Jan 30 '21 at 12:40

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

Hamit YILDIRIM · Answer 3 · 2020-02-28T09:02:53.337

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

keivan kashani · Answer 4 · 2023-06-28T07:05:22.200

2

I have this problem for add-migration correctly and update-database correctly too without error and tell me "Done" in .NET 6 project but it did not make database in sql server. My problem solved by changing options of connection string

"trustServerCertificate=true;Encrypt=False"

and after created database in my DBContext class I used:

Database.EnsureCreated();

For avoid making db again after created

edited Jun 28 '23 at 07:05

answered Jun 28 '23 at 06:52

keivan kashani

1,263
14
15

score 1 · Answer 5 · answered Feb 07 '22 at 17:35

For this helped:

comment out all of the Up() method of the initial migration file
run the application
in the database in the EFMigrationsHistory is then the first entry generated
i changed the Up() method back to original
run application again and the bug is gone

worked for me locally and on the server.

score -1 · Answer 6 · answered Dec 04 '21 at 07:42

Error Number:2714,State:6,Class:16 There is already an object named 'Employee' in the database.

I just solve issued by go to up method of Migration and Comment the Employee code which is Following

migrationBuilder.CreateTable(
                name: "Employee",
                columns: table => new
                {
                    Eid = table.Column<int>(type: "int", nullable: false),
                    FirstName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
                    LastName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false),
                    Salary = table.Column<decimal>(type: "money", nullable: false),
                    Gender = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true),
                    Did = table.Column<int>(type: "int", nullable: true),
                    Email = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true),
                    ConfirmEmail = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true),
                    LnKdProfile = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employee", x => x.Eid);
                });

Step : then i run updatate-database command it run sucessfully it gives error beacuase Employee table is already present in my database so we cannot create new table with same name .

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

6 Answers6

Linked

Related