How to create CreatedOn and UpdatedOn using EF Core 2.1 and Pomelo

Question

In Code First approach, how to define my entity so that:

• CreatedOn NOT NULL - value is generated on insert by the db with the current timestamp
• Updated NULL - value is generated on update by the db with the current timestamp

Sample Entity:

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column(TypeName = "TIMESTAMP")]
    public DateTime CreatedOn { get; set; }
    [Column(TypeName = "TIMESTAMP")]
    public DateTime UpdatedOn { get; set; }
}

DbContext:

public class MyContext : DbContext
{
    public MyContext(DbContextOptions options) : base(options) {}

    public DbSet<MyEntity> Entities { get; set; }
}

End result in the database should be:

• CreatedOn NOT NULL - Has no Extra - Default could be CURRENT_TIMESTAMP
• UpdatedOn NULL - Extra on update CURRENT_TIMESTAMP - No Default or Default is NULL

Answer 1

Issue:

I've narrowed this down to (what appears to be) a bug in Pomelo. Issue is here:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/801

The issue is that Pomelo creates a defaultValue property for DateTime and other structs when generating the migration. If a default value is set on the migration, it overrides the value generation strategy, and the SQL then looks incorrect.

The workaround is to generate the migration, and then manually modify the migrations file to set the defaultValue to null (or remove the entire line).

For example, change this:

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

To this:

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

The migration script will then spit out the correct SQL with DEFAULT CURRENT_TIMESTAMP for TIMESTAMP. If you remove the [Column(TypeName = "TIMESTAMP")] attribute, it will use a datetime(6) column and spit out DEFAULT CURRENT_TIMESTAMP(6).

SOLUTION:

I've come up with a workaround that correctly implements Created Time (updated by the database only on INSERT) and Updated time (updated by the database only on INSERT and UPDATE).

First, define your entity like so:

public class SomeEntity
{
    // Other properties here ...

    public DateTime CreatedTime { get; set; }
    public DateTime UpdatedTime { get; set; }
}

Then, add the following to OnModelCreating():

protected override void OnModelCreating(ModelBuilder builder)
{
    // Other model creating stuff here ...

    builder.Entity<SomeEntity>.Property(d => d.CreatedTime).ValueGeneratedOnAdd();
    builder.Entity<SomeEntity>.Property(d => d.UpdatedTime).ValueGeneratedOnAddOrUpdate();

    builder.Entity<SomeEntity>.Property(d => d.CreatedTime).Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    builder.Entity<SomeEntity>.Property(d => d.CreatedTime).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);
    builder.Entity<SomeEntity>.Property(d => d.UpdatedTime).Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    builder.Entity<SomeEntity>.Property(d => d.UpdatedTime).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);
}

This produces a perfect initial migration (where migrationBuilder.CreateTable is used), and generates the expected SQL:

`created_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

This should also work on migrations that update existing tables, but do make sure that defaultValue is always null.

The SetBeforeSaveBehavior and SetAfterSaveBehavior lines prevent EF from ever trying to overwrite the Created time with a default value. It effectively makes the Created and Updated columns read only from EF's point of view, allowing the database to do all of the work.

You can even extract this into an interface and extension method:

public interface ITimestampedEntity
    {
        DateTime CreatedTime { get; set; }
        DateTime UpdatedTime { get; set; }
    }

public static EntityTypeBuilder<TEntity> UseTimestampedProperty<TEntity>(this EntityTypeBuilder<TEntity> entity) where TEntity : class, ITimestampedEntity
{
    entity.Property(d => d.CreatedTime).ValueGeneratedOnAdd();
    entity.Property(d => d.UpdatedTime).ValueGeneratedOnAddOrUpdate();

    entity.Property(d => d.CreatedTime).SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    entity.Property(d => d.CreatedTime).SetAfterSaveBehavior(PropertySaveBehavior.Ignore);
    entity.Property(d => d.UpdatedTime).SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
    entity.Property(d => d.UpdatedTime).SetAfterSaveBehavior(PropertySaveBehavior.Ignore);

    return entity;
}

Then implement the interface on all of your timestamped entities:

public class SomeEntity : ITimestampedEntity
{
    // Other properties here ...

    public DateTime CreatedTime { get; set; }
    public DateTime UpdatedTime { get; set; }
}

This allows you to set up the Entity from within OnModelCreating() like so:

protected override void OnModelCreating(ModelBuilder builder)
{
    // Other model creating stuff here ...

    builder.Entity<SomeTimestampedEntity>().UseTimestampedProperty();
}