CODEWITHSUNDEEP
c#sqldatabaseasp.net-coreentity-framework-core
Kelsey Steele
Kelsey Steele

Reputation: 239

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

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!

Upvotes: 19

Views: 56743

Answers (6)

keivan kashani
keivan kashani

Reputation: 1349

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

Upvotes: 2

maerlyn
maerlyn

Reputation: 83

For this helped:

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

worked for me locally and on the server.

Upvotes: 1

Farukh Shaikh
Farukh Shaikh

Reputation: 89

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 .

Upvotes: -1

Steve Greene
Steve Greene

Reputation: 12304

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.

Upvotes: 44

Alex Potapchuk
Alex Potapchuk

Reputation: 71

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

Upvotes: 7

Hamit YILDIRIM
Hamit YILDIRIM

Reputation: 4539

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

Upvotes: 4

Related Questions