Can't use migrations in EF Core: "42P07: relation "AspNetRoles" already exists"

[murbanowicz]

I have strange issue which I can't find solution for.

The stack is: NET Core 2, EF, PostgreSQL.
I use .NET Core Identity with User : IdentityUser to extend base user model with additional fields.

After I create first migration, drop whole database and try to dotnet ef database update I always get an error: 42P07: relation "AspNetRoles" already exists

Even with this error, the database and tables are created but it makes migrations useless as it does not save applied migrations so I can't update DB with following changes...

Exception message: 42P07: relation "AspNetRoles" already exists

Stack trace:fail: Microsoft.EntityFrameworkCore.Database.Command[200102]
      Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "AspNetRoles" (
          "Id" text NOT NULL,
          "ConcurrencyStamp" text NULL,
          "Name" varchar(256) NULL,
          "NormalizedName" varchar(256) NULL,
          CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
      );
Npgsql.PostgresException (0x80004005): 42P07: relation "AspNetRoles" already exists
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__84.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "AspNetRoles" (
    "Id" text NOT NULL,
    "ConcurrencyStamp" text NULL,
    "Name" varchar(256) NULL,
    "NormalizedName" varchar(256) NULL,
    CONSTRAINT "PK_AspNetRoles" PRIMARY KEY ("Id")
);
Npgsql.PostgresException (0x80004005): 42P07: relation "AspNetRoles" already exists
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.<ExecuteNonQuery>d__84.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   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 "AspNetRoles" already exists

Steps to reproduce

Create initial migration while using Identity.
Drop whole DB or change conn string to empty db.
Try to use dotnet ef database update

Further technical details

EF Core version: (2
Database Provider: (Npgsql
Operating system: Linux, but the same issue on Windows
IDE: (e.g. Visual Studio 2015) Jetbrains Rider

[smitpatel]

You are calling migrate or ensurecreated while building service provider.
Please share code of your startup & program.cs

[ajcvickers]

Discussed in triage and this seems like it is almost certainly due to EnsureCreated or Migrate being called when the application starts, and this code may also be running when the tooling is used. See https://docs.microsoft.com/en-us/aspnet/core/migration/1x-to-2x/ for information on how to change startup code to match the patterns now supported by ASP.NET Core in 2.0.

If we missed something, then please comment with additional information and we can re-open this issue.

[MontyGvMC]

Unfortunatly this issue doesn't provide a solution yet.

I run into the exact same issue (except that in my case another table is the first in the migration and therefor the exception states a different table).

Kind of weird it is even the database from Identity Server (4).

The migration works perfectly when run against an empty DB. When run against an existing db the exception is thrown.

public static void Main(string[] args)
{

  var host = CreateWebHostBuilder(args).Build();

  using (var scope = host.Services.CreateScope())
  {
    IdDbContext context = scope.ServiceProvider.GetRequiredService<IdDbContext>();
    Console.WriteLine("migrating database ...");
    context.Database.Migrate();
    Console.WriteLine("migrating database ... DONE");
  }

  host.Run();
  
} 

IdDbContext is basically
public class IdDbContext: IdentityDbContext<ApplicationUser>, IConfigurationDbContext, PersistedGrantDbContext {

Noteworthy: the database created (which fails to migrate because of existing table) was created following the Identity Server tutorial having 3 different DBContext(s) and 3 different migrations.

Now I try to bundle the 3 different DBContext(s) into one when the migration fails. Therefor I deleted the old migrations and created a new one for the bundled IdDbContext.

Another noteworthy aspect: I do the exact same (with a different db context) like created migrations, migrate with the given code above) in a different project and there it works without problems. Can it be just a simple setting?

EF Core 2.2
Visual Studio 2017
Windows 7
Npgsql.EntityFrameworkCore.PostgreSQL 2.2.0

[MontyGvMC]

For me the problem was messing up the migrations.

I tried to create 3 migrations (the first for asp.net identity, the second + the configuration store, the third + the persisted grants db)

I removed the dbsets but forgot to remove the calls within the OnModelBuilding method. So each migration had all the tables (e.g. in the model snapshot) and therefor it seems they tried to create them multiple times. Since I changed that everything works great again...

Found the link of the original issuer on StackOverflow

[tbazen]

In my case similar error occurred because I already had the DB and Tables created but not using migration. this is how I resolved the issue

1. Add new migration
2. Manually insert the newly added migration into migration history table (__EFMigrationsHistory)

After doing this it worked fine for both adding new migrations and updating the database from the new migrations.

[justicemilitia]

In my case similar error occurred because I already had the DB and Tables created but not using migration. this is how I resolved the issue

1. Add new migration
2. Manually insert the newly added migration into migration history table (__EFMigrationsHistory)

After doing this it worked fine for both adding new migrations and updating the database from the new migrations.

Thank You. It works

[smokeelow]

In my case similar error occurred because I already had the DB and Tables created but not using migration. this is how I resolved the issue

1. Add new migration
2. Manually insert the newly added migration into migration history table (__EFMigrationsHistory)

After doing this it worked fine for both adding new migrations and updating the database from the new migrations.

That was the root cause in my case. Helped me. Thanks.

[Ntozakhe]

You are calling migrate or ensurecreated while building service provider. Please share code of your startup & program.cs

Thats why!! Thank you so much, it took me almost the whole night trying to figure this out.