Error When Updating Database with Entity Framework Core Code First

Question

I have my Domain created with N objects and these objects are converted into table in the database, in Entity Framemework Core with Code First. If I create the database with the tables it creates perfectly however if the table already exists and I try to update with a new field or with the removal of the field the command Update-Database error, saying that the table already exists(in case it checks the first table it tries to create if it already exists it gives this error). I managed the query and realized that when it generates the scrip the same does not come like this:

CREATE TABLE IF NOT EXISTS <NOME DA TABELA> {<CAMPOS>}

But this is how:

CREATE TABLE <NOME DA TABELA> {<CAMPOS>}

And I noticed that the "__Efmigrationshistory" table he creates as quoted in the first example. Someone knows how to fix this ?

Update-database -verbose -context Context
Using project '<pacote>'.
Using startup project '<pacote>'.
Build started...
Build succeeded.
C:\Program Files\dotnet\dotnet.exe exec --depsfile C:\Users\<nome>\source\repos\<projeto>\<projeto>\<projeto>\bin\Debug\netcoreapp2.2\<sistema>.deps.json --additionalprobingpath C:\Users\<nome>\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\Users\<nome>\source\repos\<sistema>\<sistema>\<projeto>\bin\Debug\netcoreapp2.2\<sistema>.runtimeconfig.json "C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.entityframeworkcore.tools\2.2.0-preview2-35157\tools\netcoreapp2.0\any\ef.dll" database update --context Context --verbose --no-color --prefix-output --assembly C:\Users\<nome>\source\repos\<sistema>\<projeto>\<sistema>\bin\Debug\netcoreapp2.2\<sistema>.dll --startup-assembly C:\Users\<nome>\source\repos\<sistema>\<projeto>\<sistema>\bin\Debug\netcoreapp2.2\<sistema>.dll --project-dir C:\Users\<nome>\source\repos\<projeto>\<projeto>\<pacote>\ --language C# --working-dir C:\Users\<nome>\source\repos\<projeto>\<projeto>--root-namespace <sistema>
Using assembly '<pacote A>'.
Using startup assembly '<pacote  B>'.
Using application base 'C:\Users\<nome>\source\repos\<projeto>\<projeto>\<pacote>\bin\Debug\netcoreapp2.2'.
Using working directory 'C:\Users\<nome>\source\repos\<sistema>\<sistema>\<pacote>'.
Using root namespace '<pacote>'.
Using project directory 'C:\Users\<nome>\source\repos\<sistema>\<sistema>\<pacote>\'.
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider...
Finding IWebHost accessor...
Using environment 'Development'.
Using application service provider from IWebHost accessor on 'Program'.
Found DbContext 'Context'.
Found DbContext 'ContextLog'.
Finding DbContext classes in the project...
Microsoft.EntityFrameworkCore.Model.Validation[20601]
      The 'bool' property 'Alugado' on entity type 'Apartamento' is configured with a database-generated default. This default will always be used for inserts when the property has the value 'false', since this is the CLR default for the 'bool' type. Consider using the nullable 'bool?' type instead so that the default will only be used for inserts when the property value is 'null'.
Microsoft.EntityFrameworkCore.Model.Validation[20601]
      The 'bool' property 'AlterarSenha' on entity type 'Conta' is configured with a database-generated default. This default will always be used for inserts when the property has the value 'false', since this is the CLR default for the 'bool' type. Consider using the nullable 'bool?' type instead so that the default will only be used for inserts when the property value is 'null'.
Microsoft.EntityFrameworkCore.Model.Validation[20601]
      The 'bool' property 'ContaAtiva' on entity type 'Conta' is configured with a database-generated default. This default will always be used for inserts when the property has the value 'false', since this is the CLR default for the 'bool' type. Consider using the nullable 'bool?' type instead so that the default will only be used for inserts when the property value is 'null'.
Microsoft.EntityFrameworkCore.Model.Validation[20601]
      The 'bool' property 'Logado' on entity type 'Conta' is configured with a database-generated default. This default will always be used for inserts when the property has the value 'false', since this is the CLR default for the 'bool' type. Consider using the nullable 'bool?' type instead so that the default will only be used for inserts when the property value is 'null'.
Microsoft.EntityFrameworkCore.Model.Validation[20601]
      The 'bool' property 'PermissaoValida' on entity type 'PermissaoConta' is configured with a database-generated default. This default will always be used for inserts when the property has the value 'false', since this is the CLR default for the 'bool' type. Consider using the nullable 'bool?' type instead so that the default will only be used for inserts when the property value is 'null'.
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.2.0-preview2-35157 initialized 'Context' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

                    SELECT CASE WHEN COUNT(*) = 0 THEN FALSE ELSE TRUE END
                    FROM information_schema.tables
                    WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema')
Using context 'Context'.
Finding design-time services for provider 'Npgsql.EntityFrameworkCore.PostgreSQL'...
Using design-time services from provider 'Npgsql.EntityFrameworkCore.PostgreSQL'.
Finding design-time services referenced by assembly '<pacote>'.
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly '<pacote>'...
No design-time services were found.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "MigrationId", "ProductVersion"
      FROM "__EFMigrationsHistory"
      ORDER BY "MigrationId";
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20181101021917_FirstMigration'.
Applying migration '20181101021917_FirstMigration'.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (112ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "Chat" (
          "Id" bigserial NOT NULL,
          "DataCriacao" timestamp without time zone NULL DEFAULT TIMESTAMP '2018-10-31 23:19:15.721042',
          "DataModificacao" timestamp without time zone NULL,
          "DataInativacao" timestamp without time zone NULL,
          "Status" integer NOT NULL DEFAULT 0,
          "Conversa" character varying(255) NOT NULL,
          "DataEnvio" text NOT NULL DEFAULT '10/31/2018 23:19:15',
          CONSTRAINT "PK_Chat" PRIMARY KEY ("Id")
      );
Npgsql.PostgresException (0x80004005): 42P07: relation "Chat" already exists
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1012
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 467
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1220
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
   at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Failed executing DbCommand (112ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Chat" (
    "Id" bigserial NOT NULL,
    "DataCriacao" timestamp without time zone NULL DEFAULT TIMESTAMP '2018-10-31 23:19:15.721042',
    "DataModificacao" timestamp without time zone NULL,
    "DataInativacao" timestamp without time zone NULL,
    "Status" integer NOT NULL DEFAULT 0,
    "Conversa" character varying(255) NOT NULL,
    "DataEnvio" text NOT NULL DEFAULT '10/31/2018 23:19:15',
    CONSTRAINT "PK_Chat" PRIMARY KEY ("Id")
);
Npgsql.PostgresException (0x80004005): 42P07: relation "Chat" already exists
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1012
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 467
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1220
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
   at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   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 contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
42P07: relation "Chat" already exists

Answer 1

I need to know if you updated your mappings with the change that was made to your entity.

If yes, you have to add a new Migration (Add-Migration 'name') and then give updade-database (not required, but your Migration will generate a repository that is easier to track within the source code, it’s up to you);