Pomelo.entityframeworkcore.mysql: DateTime default value is incorrect when using DatabaseGenerated Identity or Computed columns

[crozone]

Steps to reproduce

Create an entity with DateTime properties, and decorate them with either [DatabaseGenerated(DatabaseGeneratedOption.Identity)] or [DatabaseGenerated(DatabaseGeneratedOption.Computed)]:

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public DateTime CreatedTime { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime UpdatedTime { get; set; }

Generate a migration for the change.

Notice that the migration has produced AddColumn methods with a default value of defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified)):

migrationBuilder.AddColumn<DateTime>(
                name: "CreatedTime",
                table: "SomeTable",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

...

migrationBuilder.AddColumn<DateTime>(
                name: "UpdatedTime",
                table: "SomeTable",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.ComputedColumn);

Then, script the migration. Notice that the generated SQL uses the default value listed in the migrations file, instead of the desired CURRENT_TIMESTAMP(6) SQL.

What we want from [DatabaseGenerated(DatabaseGeneratedOption.Identity)]:

ALTER TABLE `SomeTable` ADD `CreatedTime` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6);

What we get from [DatabaseGenerated(DatabaseGeneratedOption.Identity)]:

ALTER TABLE `SomeTable` ADD `CreatedTime` datetime(6) NOT NULL;

What we want from [DatabaseGenerated(DatabaseGeneratedOption.Computed)]:

ALTER TABLE `SomeTable` ADD `UpdatedTime` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6);

What we get from [DatabaseGenerated(DatabaseGeneratedOption.Computed)]:

ALTER TABLE `devices` ADD `updated_time` datetime(6) NOT NULL DEFAULT '0001-01-01 00:00:00.000000' ON UPDATE CURRENT_TIMESTAMP(6);

The issue

The migration generator outputs a default value of defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified)) for DateTime columns. This causes the MySqlMigrationsSqlGenerator ColumnDefinition method to avoid setting CURRENT_TIMESTAMP(6) as the default SQL because it believes a default value has already been set. defaultValue must be null for the following branch to trigger:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/2c5a931ea2b5170e29ac7f1b8f36cc7cd0de2728/src/EFCore.MySql/Migrations/MySqlMigrationsSqlGenerator.cs#L914-L916

Workaround:

The migration's defaultValue parameter can manually be set to null after the migration has been created (or the entire line can be deleted):

migrationBuilder.AddColumn<DateTime>(
                name: "UpdatedTime",
                table: "SomeTable",
                nullable: false,
                defaultValue: null
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.ComputedColumn);

This generates the correct SQL:

ALTER TABLE `SomeTable` ADD `UpdatedTime` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6);

Attempting to set the default value by either using the [DefaultValue(null)] attribute on the entity property, or by modifying OnModelCreating with builder.Entity<SomeTable>().Property(dh => dh.CreatedTime).HasDefaultValue(null); and builder.Entity<SomeTable>().Property(dh => dh.UpdatedTime).HasDefaultValue(null); does not work, and actually has the other negative side effect of disabling the MySqlValueGenerationStrategy annotation completely. Likewise, attempting to use a nullable DateTime? as the property type has the same effect as .HasDefaultValue(null): the ValueGenerationStrategy annotation is completely removed from the migration.

Further technical details

MySQL version: MariaDB 10.1
Pomelo.EntityFrameworkCore.MySql version:
Microsoft.AspNetCore.App version: 2.2.0

[crozone]

Further notes: This does not occur during the initial migration where migrationBuilder.CreateTable() is used:

created_time = table.Column<DateTimeOffset>(nullable: false)
    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn),
updated_time = table.Column<DateTimeOffset>(nullable: false)
    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.ComputedColumn)

It only occurs when the migrationBuilder.AddColumn() method is used. So, another (not nice) workaround is to reset all migrations and make sure the tables are created with these columns already present so they never have to be added at a later date.

[lauxjpn]

Now this is what I call a perfect description (... or pod-racing)!

[lauxjpn]

I have successfully reproduced this issue on my end.
Everything you expect is correct and in line with the docs.
I am picking this one up for the 3.0.0-rc2 release.

[lauxjpn]

Thanks @crozone for reporting this issue!
It has been fixed in #870.

[crozone]

Fantastic!!