__EFMigrationsHistory columns are snake_cased, upgrading needs manual alter script

An extra gotcha that maybe worth mentioning in the readme:

Referencing this package also snake_cases the columns in the __EFMigrationsHistory table (though interestingly, not the table name itself). This means migrations fail as they can not run a version check to obtain the current database version.

This was solved by running this script on the DB

alter table "__EFMigrationsHistory"
rename column "MigrationId" to migration_id;

alter table "__EFMigrationsHistory"
rename COLUMN "ProductVersion" to product_version

I have been using code very similar to https://github.com/aspnet/EntityFrameworkCore/issues/5159#issuecomment-522223918 to provide snake case columns in my DB, so all columns are already snake cased.

Moving over to this package did try to recreate primary keys (as warned in the readme) however in my case postgres already had the constraint name in lower case so commenting out the generated migration and letting it run though as a no-op has successfully aligned the stars for me (on a very small demo codebase).

Had the constraint name actually needed an update i would have replace the generated migration with this rather than running the generated drop/create

migrationBuilder.Sql("ALTER TABLE customers RENAME CONSTRAINT PK_customers TO pk_customers");

Console output from running dotnet ef database update --context DemoMigratorDbContext

Executing DbCommand [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');
Executing DbCommand [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');
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT migration_id, product_version
FROM "__EFMigrationsHistory"
ORDER BY migration_id;
Failed executing DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT migration_id, product_version
FROM "__EFMigrationsHistory"
ORDER BY migration_id;
Npgsql.PostgresException (0x80004005): 42703: column "migration_id" does not exist
   at Npgsql.NpgsqlConnector.<>c__DisplayClass163_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass163_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   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.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column "migration_id" does not exist
    Hint: Perhaps you meant to reference the column "__EFMigrationsHistory.MigrationId".
    Position: 8
    File: parse_relation.c
    Line: 3293
    Routine: errorMissingColumn
42703: column "migration_id" does not exist

Issue Analytics

State:
Created 4 years ago
Reactions:20
Comments:18 (2 by maintainers)

Top GitHub Comments

8reactions

alfegcommented, Nov 25, 2021

I’m using the following workaround that is described in docs https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table

    public class CamelCaseHistoryContext : NpgsqlHistoryRepository
    {
        public CamelCaseHistoryContext(HistoryRepositoryDependencies dependencies) : base(dependencies)
        {
        }

        protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> history)
        {
            base.ConfigureTable(history);

            history.Property(h => h.MigrationId).HasColumnName("MigrationId");
            history.Property(h => h.ProductVersion).HasColumnName("ProductVersion");
        }
    }

// later in code
opts.UseNpgsql()             
                .ReplaceService<IHistoryRepository, CamelCaseHistoryContext>()
                .UseSnakeCaseNamingConvention();

Replace NpgsqlHistoryRepository with You DB provider implementation. Take into account that those classes are internal EF API and subject to change.

7reactions

aph5ntcommented, Aug 25, 2021

any update on this?