Build a better way to manage custom migration operations

[GabrielHSFerreira]

Problem:
Currently, it is common to call .Sql(), or make an extension method for MigrationBuilder, and call inside some generated migration when we need to execute some custom operation using the migration feature. That can become a problem on a scenario where resetting the migration history is required, since one would have to review all migrations in search of custom calls.

Seeding of data, creation of procedures, views, full-text catalogs and any other model changes operation that is not natively supported by Entity Framework Core can be easily lost.

Proposal:
What I propose is that we build a feature for managing custom commands similar to entities fluent API configuration and EF should include these custom commands in the migrations and snapshot as well update when something changes.

So, just like we can do:
modelBuilder.Entity<SomeEntity>().SomeConfiguration();

Or:

public void SomeEntityConfiguration : IEntityTypeConfiguration<SomeEntity>
{
    public void Configure(EntityTypeBuilder<SomeEntity> builder)
    {
        builder.SomeConfiguration();
    }
}

We would do:
modelBuilder.Custom<CustomTypeIdentifier>().RunSql("CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;");

Or:

public void SomeEntityConfiguration : ICustomTypeConfiguration<CustomTypeIdentifier>
{
    public void Configure(CustomTypeBuilder<CustomTypeIdentifier> builder)
    {
        builder.RunSql("CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;");
    }
}

Please do not get too attached to my poor example. It is just a way I found to relate to fluent entity configuration.

[roji]

I'm not sure what in the above proposal solves the problem of resetting the migrations... It's certainly easy to wrap custom SQL in extension methods (or some builder API, or whatever), but addressing the migration reset scenario probably requires a much more fundamental design rethink.

[GabrielHSFerreira]

I'm not sure what in the above proposal solves the problem of resetting the migrations... It's certainly easy to wrap custom SQL in extension methods (or some builder API, or whatever), but addressing the migration reset scenario probably requires a much more fundamental design rethink.

I completely agree that is a hard task. My proposal above is about a configuration interface for the users. Actually I do not know enough about the migration generation engine to propose the low level design, so my intent is kickoff the discussion.

[GabrielHSFerreira]

As a huge EF fan (both Vanilla and Core), I believe it can improve even more it's code first capabilities, making it easy to fully manage database design.

[roji]

I'm pretty sure we have something about this already in the backlog; at the very least it has been raised many times before, notably as part of the discussion around migrations squashing (#2174).

@maumar does this specifically ring a bell in terms of duplicates?