Replacing of the IMigrationsSqlGenerator Service does not Work

[alc86]

Currently the relacement of the IMigrationsSqlGenerator does not work with the npgsql provider. I tried both solutions of the following ticket: [https://github.com/dotnet/efcore/issues/6083]

[roji]

That issue is 4 years old and the answer is no longer relevant. You can use ReplaceService in your OnConfiguring to easily replace any service as below. Note that there is nothing Npgsql-specific about this.

class Program
{
    static async Task Main(string[] args)
    {
        await using var ctx = new BlogContext();
        await ctx.Database.EnsureDeletedAsync();
        await ctx.Database.EnsureCreatedAsync();
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .ReplaceService<IMigrationsSqlGenerator, CustomMigrationsSqlGenerator>()
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class CustomMigrationsSqlGenerator : NpgsqlMigrationsSqlGenerator
{
    public CustomMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IMigrationsAnnotationProvider migrationsAnnotations, INpgsqlOptions npgsqlOptions)
        : base(dependencies, migrationsAnnotations, npgsqlOptions) {}

    protected override void Generate(
        CreateTableOperation operation,
        IModel model,
        MigrationCommandListBuilder builder,
        bool terminate = true)
    {
        builder.AppendLine("-- My custom comment");

        base.Generate(operation, model, builder, terminate);
    }
}

[alc86]

I have tried your solution but this does not work as expected. The constructor of the CustomMigrationsSqlGenerator is called but not the overwritten Generate method. I guess that the CustomMigrationsSqlGenerator will be replaced by the default implementation.

My Goal is to create an CustomMigrationsSqlGenerator which handles my custom Annotation which i want to use to create the Index: CREATE UNIQUE INDEX i_nulltest ON "Com_User" (("UserName" IS NULL), ("DeletedDate" IS NULL)) WHERE "UserName" IS NULL OR "DeletedDate" IS NULL;

[roji]

@alc86 I tested my code sample above, and I can clearly see the custom comment coming out when generating SQL for creating a new table - can you please run it and confirm? If the problem is only when generating migrations from the commandline (and not programmatically as in the sample above), you may want to also take a look at this doc page.

My Goal is to create an CustomMigrationsSqlGenerator which handles my custom Annotation which i want to use to create the Index

If I understand you correctly, you're trying to create expression indexes (as you've asked in #1486). If so, then that is not a good use case for extending your own MigrationsSqlGenerator. NpgsqlMigrationsSqlGenerator is internal, so it could change at any time and break your application. It is recommended to use raw SQL to create the expression index as suggested in #1486.

Finally, the index above does not seem like it's very appropriate as an expression index. It seems that you're trying to create an index which speeds up queries that check if both UserName and DeletedDate are NULL. If so, then a simple composite index over these two columns should be sufficient, and more or less as performant as the index above. Have you tried that and compared performance?

To summarize, I'd leave migration generation aside for now, understand exactly what it is that you're trying to achieve and what's the best way to do that. Please feel free to post back here if you need more advice/guidance.

[alc86]

The speed of the queries is only at the second place I need a real unique index which also includes the NULL values https://www.enterprisedb.com/postgres-tutorials/postgresql-unique-constraint-null-allowing-only-one-null like MS SQL Server does and for this I need this index. If have a better idea please let me know.

[roji]

@alc86 yeah, then you're looking for an expression index, which the provider doesn't create (#1486). But it's really trivial to add it yourself with a raw SQL migration, as I wrote above.

[BlackBearFTW]

I know this issue is pretty old, but is it possible to append something to the end of the sql script or after the table sql is generated? Everything like this will be appended before the actual base sql query is generated. Like your comment, it will be placed before the CREATE TABLE syntax, I want to somehow place my comment after that syntax.

[roji]

@BlackBearFTW yes - as the code above shows, you can replace the IMigrationsSqlGenerator with your own custom implementation (which derives from NpgsqlMigrationsSqlGenerator). In that implementation you can do whatever customizations you need to.