How to fix there is an already object named "Books" in database-CodePudding

I created small web API but when I'm going to migrate my model class into physical database it gives me an error. It's okay with project when I give Add-Migration. This error occurs when I trying to give Update-Database:

Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (111ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Books] (
          [Id] int NOT NULL IDENTITY,
          [Title] nvarchar(100) NULL,
          [Author] nvarchar(100) NULL,
          [Description] nvarchar(100) NULL,
          CONSTRAINT [PK_Books] PRIMARY KEY ([Id])
      );
Failed executing DbCommand (111ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Books] (
    [Id] int NOT NULL IDENTITY,
    [Title] nvarchar(100) NULL,
    [Author] nvarchar(100) NULL,
    [Description] nvarchar(100) NULL,
    CONSTRAINT [PK_Books] PRIMARY KEY ([Id])
);
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Books' in the database.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   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.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:0b41a251-3fe9-4c55-a2fd-118e7348a564
Error Number:2714,State:6,Class:16
There is already an object named 'Books' in the database.

In my SQL Server database there are no tables named "Books". Why do I get this error and how can I fix it?

CodePudding user response：

You might have somewhere in the process a table creation or maybe you are looking at the wrong database. You can check the connection string to see which database it calls and see if the table exists there.

You also might add a check if the table exists before the creation.

IF NOT EXISTS
(
    SELECT  1
    FROM    sys.objects 
    WHERE   object_id = OBJECT_ID(N'[dbo].[Books]')
)
BEGIN
    CREATE TABLE [dbo].[Books]
    ....
END

CodePudding user response：

Things like this might happen when you update the content and then try delete files or or objects without trying to revert the context correctly. Below I will go through methods for fixing this issue, or issues you might have.

Solution 1 - Delete It

This methods is what I use to clear conflicts on tables where I made errors and updating it isn't really a choice. But I would not recommend this method until you have a better grasp of how EF works.

When EF says the object already exists - it might not be referring to the table, but the object defined in one of your migration files. But to clear an object completely (manually) there are two things you:

Delete the Migration File in your project where book might be defined.
Delete the migration record in your Database (Yes there is a table for that).

Solution 2 - Revert it

This is the recommended one I would say someone new to EF core should use:

Revert your Migration with: Update-Database <prior-migration-name>. Select one of the earlier migration before you ever defined the Books object. Then update the migration again and it should work - if you haven't defined DbSet<Books> twice in your context.

Solution 3 - Roll with it

Another one for beginners to use is to download the context from the database using the scaffolding. This will download the database you are using with all the objects in it - where you can then change it as you desire.

Scaffold-DbContext "<Connection String>" Microsoft.EntityFrameworkCore.SqlServer -OutputDir <Directory>

Careful when using this method is it can replace your current context.

Suggested

I would say use method 2 first, if that doesn't work, then method 1 and finally method 3.