如何使用EF Core 2.1和Pomelo创建CreatedOn和UpdatedOn [英] How to create CreatedOn and UpdatedOn using EF Core 2.1 and Pomelo

问题描述

在代码优先"方法中，如何定义我的实体，以便:

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

• CreatedOn NOT NULL-值是由数据库在插入时使用当前时间戳生成的.
• Updated NULL-值是由数据库使用当前时间戳记更新时生成的.

• 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

样本实体:

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:

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非空-没有多余内容-默认值可能是CURRENT_TIMESTAMP
• UpdatedOn NULL-额外更新CURRENT_TIMESTAMP-无默认值或默认值为NULL

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

推荐答案

问题:

我已经将其缩小为(似乎是)柚子中的一个错误.问题在这里:

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

问题在于，Pomelo在生成迁移时会为DateTime和其他结构创建defaultValue属性.如果在迁移过程中设置了默认值，它将覆盖值生成策略，然后SQL看起来不正确.

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.

解决方法是生成迁移，然后手动修改迁移文件以将defaultValue设置为null(或删除整行).

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).

例如，更改此:

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);

对此:

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

然后，迁移脚本将使用DEFAULT CURRENT_TIMESTAMP为TIMESTAMP吐出正确的SQL.如果删除[Column(TypeName = "TIMESTAMP")]属性，它将使用datetime(6)列并吐出DEFAULT CURRENT_TIMESTAMP(6).

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).

解决方案:

我想出一种解决方法，可以正确实现Created Time(仅在INSERT上由数据库更新)和Updated time(仅在INSERT和UPDATE上由数据库更新).

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; }
}

然后，将以下内容添加到OnModelCreating():

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.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
    builder.Entity<SomeEntity>.Property(d => d.CreatedTime).Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;
    builder.Entity<SomeEntity>.Property(d => d.UpdatedTime).Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
    builder.Entity<SomeEntity>.Property(d => d.UpdatedTime).Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;
}

这会产生完美的初始迁移(使用migrationBuilder.CreateTable)，并生成预期的SQL:

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),

此应该还可用于更新现有表的迁移，但请确保defaultValue始终为空.

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

PropertySaveBehavior行可防止EF尝试用默认值覆盖Created时间.从EF的角度来看，它有效地使创建"和更新"列只读，从而使数据库能够完成所有工作.

The PropertySaveBehavior 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).Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
    entity.Property(d => d.CreatedTime).Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;
    entity.Property(d => d.UpdatedTime).Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
    entity.Property(d => d.UpdatedTime).Metadata.AfterSaveBehavior = 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; }
}

这使您可以从OnModelCreating()内部设置实体，如下所示:

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();
}

在问题799 之后，这也将与DateTimeOffset一起使用.是固定的.

This will also work with DateTimeOffset after issue 799 is fixed.

这篇关于如何使用EF Core 2.1和Pomelo创建CreatedOn和UpdatedOn的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！