EF Core 2 Provider specific HasDefaultValueSql

Question

I am trying to use my DbContext with multiple providers. So far everything works okay, except when using HasDefaultValueSql().

What I am trying to do is to have a property AuditTimestamp that has the current time when added (and saved) on the dbContext. This can easily be done using something like this that works for the SQL Server provider

modelBuilder
   .Entity(entityType.ClrType)
   .Property(nameof(AuditBase.AuditTimestamp))
   .HasDefaultValueSql("SYSUTCDATETIME()");

But when trying to use the same dbContext with SqLite provider, it doesn't work since SYSUTCDATETIME isn't a valid SQL function in SqLite.

Based on this question I tried to do

modelBuilder
   .Entity(entityType.ClrType)
   .Property(nameof(AuditBase.AuditTimestamp))
   .HasDefaultValueSql(Database.IsSqlite() ? "DATETIME('now')" : "SYSUTCDATETIME()");

But it generates this in the migrations

b.Property<DateTime>("AuditTimestamp")
   .ValueGeneratedOnAdd()
   .HasDefaultValueSql("SYSUTCDATETIME()");

because I use a SQL Server connection string when generating the migrations. This implies that I need different sets of migrations based on what provider I use which shouldn't be the case.

Based on this question it is possible to have provider specific configurations in the migration files but the example is based on auto generated Id column and I don't know how to apply this to HasDefaultValueSql()

From here I found that this might work

HasDefaultValueSql("CURRENT_TIMESTAMP")

But I would like a more general solution for when you have two (or more) providers that don't share same SQL function names.

The providers I use is SQL Server for production and SqLite for unit testing. EF Core is version 2.2

Here is the EF Core designers vision - [Migrations with Multiple Providers](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=dotnet-core-cli) — Ivan Stoev, Jan 04 '20 at 18:07
@IvanStoev, could you maybe give an answer that explains how to use annotations from your link? — smok, Jan 08 '20 at 10:59
Does CURRENT_TIMESTAMP and SYSUTCDATETIME deliver the same values? — liqSTAR, Apr 16 '20 at 08:49

score 0 · Answer 1 · answered Jan 04 '20 at 19:34

So it seems the prescribled way is to edit migrations by hand (as you pointed in this answer and Ivan suggests in the comments to your question) I do however believe there's a way to override actual SQL code generation on the execution stage.

The main thing you want to look at will be the MigrationsSqlGenerator class. It builds SQL for each type of migration commands that needs to be run. Therefore in your case overriding a DefaultValue will probably suffice:

 public class CustomMigrationsSqlGenerator : MigrationsSqlGenerator
    {
        public CustomMigration(MigrationsSqlGeneratorDependencies dependencies) : base(dependencies)
        {
        }

        /// <param name="defaultValue">The default value for the column.</param>
        /// <param name="defaultValueSql">The SQL expression to use for the column's default constraint.</param>
        /// <param name="builder"></param>        
        /// <see cref="https://github.com/aspnet/EntityFrameworkCore/blob/v2.2.8/src/EFCore.Relational/Migrations/MigrationsSqlGenerator.cs#L1407"/>
        protected override void DefaultValue(object defaultValue, string defaultValueSql, MigrationCommandListBuilder builder)
        {
            Debugger.Launch();
            if (defaultValueSql != null) // I assume you only want to adapt specific values rather than whole type
            {
                if (Dependencies.SqlGenerationHelper is SqliteSqlGenerationHelper)
                { 
                builder
                    .Append(" DEFAULT (") 
                    .Append(defaultValueSql) //replace with your specific provider logic
                    .Append(")");
                    return;
                } else if (Dependencies.SqlGenerationHelper is SqlServerSqlGenerationHelper)
                {
                    builder
                        .Append(" DEFAULT (") 
                        .Append(defaultValueSql) //replace with your specific provider logic
                        .Append(")");
                    return;
                }
            }
            //fall back to default implementation
            base.DefaultValue(defaultValue, defaultValueSql, builder);
        }
    }

now that we've got a custom migration generator, we need to replace EF's default implementation:

var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
optionsBuilder.UseWhateverDbProviderForMigrations();
optionsBuilder.ReplaceService<IMigrationsSqlGenerator, CustomMigrationsSqlGenerator>();
var context = new MyDbContext(optionsBuilder.Options);

I don't know enough on how you bootstrap the DbContext into your application and how you invoke the migrations, but assuming you are calling it from Package Manager console, the following trick will allow you to hijack the control:

 public class MigrationStartup: IDesignTimeDbContextFactory<MyDbContext>
    {
        public MyDbContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
            optionsBuilder.UseWhateverDbProviderForMigrations();
            optionsBuilder.ReplaceService<IMigrationsSqlGenerator, CustomMigration>(); // this is the important bit

            return new MyDbContext(optionsBuilder.Options);
        }
    }

This approach can be extended to other types of SQL generated, by simply providing a respective override.

One thing to note: between EF 2.2 and EF 3 the DefaultValue() method signature has changed. So I guess I should warn you this is more of a hack and comes with certain support implications.

I have not tried your approach but even if it works it seems to be a lot of work for something that the framework should be able to handle in a simpler way. — smok, Jan 08 '20 at 10:56

EF Core 2 Provider specific HasDefaultValueSql

1 Answers1