npgsql: EF7: Running a migration on an empty database fails

I have followed the steps listed in the official EF7 docs to setup a new database and the initial migration: http://ef.readthedocs.org/en/latest/getting-started/osx.html. Except of course I am using the Npgsql provider instead of SQLite. When I attempt to run dnx ef database update to setup the database schema for the first time, it fails with the following error message:

ERROR:  relation "__EFMigrationsHistory" does not exist at character 45
STATEMENT: 
  SELECT "MigrationId", "ProductVersion"
  FROM "__EFMigrationsHistory"
  ORDER BY "MigrationId"

I don’t get much of a stacktrace, but this is what I can see of it:

Using context 'AuthDbContext'.
Using database 'postgresdb' on server 'db'.
Npgsql.NpgsqlException: 42P01: relation "__EFMigrationsHistory" does not exist
  at Npgsql.NpgsqlConnector.DoReadSingleMessage (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage) [0x00000] in <filename unknown>:0 
  at Npgsql.NpgsqlConnector.ReadSingleMessageWithPrepended (DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage) [0x00000] in <filename unknown>:0

I’m assuming it is trying to query the __EFMigrationsHistory table so it can decide whether it needs to run migrations or not. I think the first thing it should do is check to see if that table exists. If it doesn’t exist, then create it.

It is also worth noting that Postgres is case sensitive when surrounding entities with quotes. It is possible that the table is being created without quotes and then queried with quotes or vice versa.

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 16 (4 by maintainers)

Most upvoted comments

It seems possible to customize the __EFMigrationsHistory name and schema through (https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table)

I have used 2 different schemas for 2 differents app requiring migration. It seems to work

services.AddDbContext<XXX>(options =>
      options.UseNpgsql(   
             sessionDBConnectionStringDecrypted,
             b =>
             {   
                b.MigrationsAssembly(migrationsAssembly);
                b.MigrationsHistoryTable("__EFMigrationsHistory", "MySchema");
             }
      )
 );

spineau59 on Aug 10, 2021

This issue isn’t completely fixed as of Npgsql.EntityFrameworkCore.PostgreSQL version 1.1.0. The system attempts to find out whether __EFMigrationsHistory exists by running a query like this:

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');

Unfortunately this will match any __EFMigrationsHistory, even if it’s in the wrong schema and won’t be located through the search path. This means that you can’t install two EntityFramework applications in the same database, because as soon as one __EFMigrationsHistory gets created, the bug comes back and you can’t create another one.

PeteX on Jan 1, 2017

As a work around you can create the table manually.

CREATE TABLE "__EFMigrationsHistory" (
    "MigrationId" text NOT NULL,
    "ProductVersion" text NOT NULL,
    CONSTRAINT "PK_HistoryRow" PRIMARY KEY ("MigrationId"));

ChrisSmith on Nov 14, 2015