Postegres ef-core update database produce "42P01: relation "__EFMigrationsHistory" does not exist" error

Question

Setup

Asp.NET core website 2.2 and EF Core 2.2
Postgresql database with multiple schemas and one of the schema already has __EFMigrationsHistory table

---

when trying

Add-Migration x1 -Context YodaContext it works

but when trying the following statement
Update-Database -Context YodaContext for the first time (I do not have any tables in this schema this is the first update-database) I am seeing the following error.

Failed executing DbCommand (85ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Npgsql.PostgresException (0x80004005): 42P01: relation "__EFMigrationsHistory" does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- 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 Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   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 1218
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.GetAppliedMigrations()
   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)
42P01: relation "__EFMigrationsHistory" does not exist

What I did

1. I searched this error and I found this bug on github the suggested solution there was to create the table manually, and I did that, but this did not solve the problem.

2. I also tried to open a new .NET 5 project and install the latest version of the Npgsql.EntityFrameworkCore.PostgreSQL v 5.0.0 provider to connect to that database and I still facing this problem

---

this question does not solve the problem EF Core - Table '*.__EFMigrationsHistory' doesn't exist

Bowlin

Does your database already exists, and which tables does it have? You may be trying to run Update-Database on a database that already has tables, but does not yet have the __EFMigrationsHistory table. Try executing this on a database that doesn't yet exist, and it should work. – Fibre

@ShayRojansky Does your database already exist? Yes, and which tables does it have? it has multiple schemas many of them have __EFMigrationsHistory table, Try executing this on a database that doesn't yet exist,Even if it works, and I think it will, this will not solve my problem, because I am working with a very huge enterprise that provides me with the database – Errol

you seem to be trying to do a multi-tenant setup, where each schema has its own __EFMigrationsHistory table and is somehow supposed to migrate independently of the others - that is not supported. EF expects to manage the entire database - including all schemas - via one migration set, tracked by one __EFMigrationsHistory table. – Fibre

@ShayRojansky really thank you for your time, the problem solved from the enterprise end, they deleted that schema and they created it again, and maybe they did some other things (which I have no idea about) after than the update-database statement worked perfectly good. I did not do anything special from my end, I will post this as solution for my problem – Errol

Answer 1

I faced this problem while working with a very huge enterprise, so the solution applied by the enterprise themselves, they deleted the schema and created it again (as they told me) maybe they did some other stuff, I do not know.

After that everything went well, and the Update-Database statement worked perfectly well.

Bowlin

Answer 2

I had same problem because my database (POSTGRESQL) had another schema that contains ____EFMigrationsHistory table. I think it is a provider's bug, in my case postgresql provider. I created same table manually on new schema then update database command executed successfully

Fatalism

Man! Two hours thinking that I was forgetting of doing something in my new project. Thanks! – Summerville

Answer 3

I also have the same issue, but I don't have permissions to delete schema and create it again in my company. I solved problem in a another way:

1. I run command to execute migration. Add-Migration InitialIdentityUser -Context IdentityUserDbContext
2. After that, I use tricky command Script-Migration -from 0. This command generate bunch of sql queries to create schema and tables.
3. I use pretty cool tool - flyway for my migrations https://flywaydb.org/
4. Execute baseline command with flyway tool.

flyway baseline -locations=filesystem:. -url=... -user=... -password=... -baselineVersion="001" -schemas=...

5. Finally, execute migrate command to migrate all sql tables.

flyway migrate -locations=filesystem:. -url=... -user=... -password=... -schemas=...

Befitting