__EFMigrationsHistory columns are snake_cased, upgrading needs manual alter script

[williamdenton]

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 dotnet/efcore#5159 (comment) 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

[JwanKhalaf]

@williamdenton isn't this the wrong way around?

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

Shouldn't you be renaming it back to MigrationId?

You are right in raising this issue though. I am working with IdentityServer4 and that has two db contexts.

One set of migrations works, but the other fails because of the column name issue.

https://i.imgur.com/q35bLW3.png

[williamdenton]

@JwanKhalaf in my case i already had a __EFMigrationsHistory table, after adding the package, the ef query that inspects this table for migrations required snake cased columns

[JwanKhalaf]

ah I see @williamdenton. Am I right in thinking that if one wishes to avoid manual intervention, one shouldn't currently use this package?

[roji]

Apologies, I was accidentally not subscribed to notifications on this repo, will try to investigate ASAP.

[williamdenton]

@roji it might be useful to have a configuration object that controls which parts of the schema have the naming convention applied. then people can opt in to the bits they need/want, or migrate in a controlled manner

[roji]

@williamdenton allowing fine-grained configuration of which parts of the schema are affected seems a bit much to me... I have a hard time imagining a situation where people want to snake_case only columns but not tables as part of a gradual migration (but am open to hear otherwise!).

For __EFMigrationHistory specifically, I have to investigate since this table has a specific meaning to EF Core, and I'm not sure how flexible things are there. It doesn't seem too terrible to leave this table outside the naming convention, as it's not exactly part of the model etc. But I'll definitely look into it.

[etiennecl]

Any news on this?

[joaopgrassi]

It's probably good to consider the default behavior with the "fix": I'll probably use the same approach as @williamdenton and rename my migration table so I can continue working with ef commands. I'd assume most ppl will do the same since it's nice to use this library and I don't want to wait for a fix on this.
The problem I can forsee though is:

if I create a new database from scratch, the columns now are already created with the snake case convention.

In cases where the database already exists (my case) the change suggested by @williamdenton will not be part of a migration but just a one time change, because I want to support ppl from creating a new db from scratch and since the app will be using the library all will be fine.

If we say: We are not going to touch the __EFMigrationHistory table in this library, ppl that applied the fix now will again have migration problems.

Hopefully I could explain the issues in a clear way.

[sangxxh]

I think it'd be great if there's an option to avoid altering the __EFMigrationsHistory table.

In my case, I use IdentityServer4, running migrations for IdentityServer4 DB context will throw:

42703: column "migration_id" of relation "__EFMigrationsHistory" does not exist

So I tried manually changing the columns back to how they were:

ALTER TABLE "__EFMigrationsHistory" RENAME COLUMN "migration_id" TO "MigrationId";
ALTER TABLE "__EFMigrationsHistory" RENAME COLUMN product_version TO "ProductVersion";

Now running IdentityServer4 migrations will work:

dotnet-ef database update -c ConfigurationDbContext
dotnet-ef database update -c PersistedGrantDbContext

But this causes a new problem where if I create a new migration and remove it during development, it'll throw this error:

dotnet-ef migrations remove -c ApplicationDbContext
Build started...
Build succeeded.
Npgsql.PostgresException (0x80004005): 42703: column "migration_id" does not exist

I'll have to stick with using PascalCase for now

[leoskey]

@hantatsang
try it

services.AddIdentityServer()
    .AddConfigurationStore(options =>
    {
        options.ConfigureDbContext = builder =>
        {
            builder.UseSnakeCaseNamingConvention();
            builder.UseMySql(connectionString,
                sql => sql.MigrationsAssembly(migrationsAssembly));
        };
    })
    .AddOperationalStore(options =>
    {
        options.ConfigureDbContext = builder =>
        {
            builder.UseSnakeCaseNamingConvention();
            builder.UseMySql(connectionString,
            sql => sql.MigrationsAssembly(migrationsAssembly));
        };
    })

[Stealcase]

TEMP WORKAROUND:

1. add ".UseSnakeCaseNamingConvention()"
2. Create the migration: dotnet ef migrations add RenameToSnakeCase
3. Remove the line "UseSnakeCaseNamingConvention:
   (opt => opt.UseNpgsql(connectionStr).UseSnakeCaseNamingConvention())
4. Update the database: dotnet ef database update
5. Re-add the line "UseSnakeCaseNamingConvention()

This Creates your migration with the renaming, and when you actually apply it, you don't want EntityFramework to be trying to access column names that don't exist yet ("migration_id")

So you apply it without using the "UseSnakeCaseNamingConvention(), because dotnet ef will build your project, and won't be trying to access a database with the name "migration_id" this time.

You have to do this song and dance EVERY TIME you want to perform Migrations.

Might be easier to just rename your __EFMigrations columns like @williamdenton suggested
But I don't know if this will cause other issues ¯_(ツ)_/¯

[cbordeman]

When I first apply the naming conventions extension, I am seeing:

SELECT migration_id, product_version
FROM "__EFMigrationsHistory"
ORDER BY migration_id;

Error: column "migration_id" does not exist

[kreditkenny]

what some people mentioned on this thread, the migrations table should be ignored from naming conventions.

[aph5nt]

any update on this?

[humleflue]

Any update on this?

calcasa@5594847 looks interesting.

[schmitch]

what some people mentioned on this thread, the migrations table should be ignored from naming conventions.

well I would not prefer it. I prefer that it also renames it, but a flag would be ok-ish

[ivarlovlie]

I just created a new migration (the first on 6.0) and tried to apply it to a database that uses snake case naming from before. What happened is that migrations got applied without the snake casing and according to the migration history spanning longer back than when the database was renamed to snake case, this happened due to efcore not picking up on the __efmigrationshistory table and assuming that the database was "fresh". The fix was renaming __efmigrationshistory to __EFMigrationsHistory.
The columns in __EFMigrationsHistory did not need to be updated. Could look like efcore is case-sensitive on 6.0 and upwards because the lower-cased __EFMigrationsHistory worked fine on 5.0.

[alfeg]

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.

[Leonardo-Lima-Pontes]

@alfeg worked to me!

[rdehouss]

@alfeg's solution works but let's pay attention to https://www.npgsql.org/efcore/api/Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlHistoryRepository.html and the warning

warning EF1001: Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlHistoryRepository is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release

A cleaner solution from EFCore would be better :)