How to use ValueGeneratedOnUpdate() correctly?

Question

I'm using EF Core 2.1.0-preview1-final CF approach with a MS SQL Server 2014 and want to set on-update a time stamp in one column automatically. I already use ValueGeneratedOnAdd without any issues. When I insert a new record into my database the InsertDateTimeUtc column has the current UTC date and time value, the UpdateDateTimeUtc column has value 0001-01-01 00:00:00.0000000, because it is a required column.

Here is my test model (entity class).

public class Person
{
    public string Name { get; set; }

    public DateTime InsertDateTimeUtc { get; set; }

    public DateTime UpdateDateTimeUtc { get; set; }


    public static void OnModelCreating(ModelBuilder modelBuilder)
    {
        var entity = modelBuilder.Entity<Person>();

        entity.Property(p => p.Name)
              .IsRequired();

        entity.Property(p => p.InsertDateTimeUtc)
              .HasDefaultValueSql("GETUTCDATE()")
              .ValueGeneratedOnAdd()
              .IsRequired();

        entity.Property(p => p.UpdateDateTimeUtc)
              .HasDefaultValueSql("GETUTCDATE()")
              .ValueGeneratedOnUpdate()
              .IsRequired();
    }
}

As far as I tested ValueGeneratedOnUpdate does not work like I expected. If I update a record I would expect that the column UpdateDateTimeUtc will get the current UTC date and time value, but it does not change. The record (property Name) will be updated successfully - I set another value for property Name and could evaluate it.

I could found this SO answer that contains following quote which seems to be from this MS doc article - Generated Values.

This just lets EF know that values are generated for added or updated entities, it does not guarantee that EF will setup the actual mechanism to generate values.

But in mentioned MS doc article I could not find any information about the usage of ValueGeneratedOnUpdate. I already tried to use ValueGeneratedOnAddOrUpdate - too without success.

Well, has somebody a hint how I could set a value for column UpdateDateTimeUtc in database automatically without any logic (like person.UpdateDateTimeUtc = DateTime.UtcNow;) in code please? Or could provide somebody a link where I could read further information?

Answer 1

The Generated values documentation topic (the link mentioned in the question) contains the following Warning:

How the value is generated for added and updated entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, while others will require you to manually setup how the value is generated.

For example, when using SQL Server, byte[] properties that are set as generated on add or update and marked as concurrency tokens, will be setup with the rowversion data type - so that values will be generated in the database. However, if you specify that a DateTime property is generated on add or update, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE() (see Default Values) to generate values for new rows. You could then use a database trigger to generate values during updates (such as the following example trigger).

followed by example trigger code.

So basically you are expected to create trigger for value generation on update. EF Core will just read back the value from the database after updating the record (similar to identity and calculated columns).

In your example, modify the generated migration Up method as follows:

migrationBuilder.CreateTable(
    name: "Person",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
        InsertDateTimeUtc = table.Column<DateTime>(nullable: false, defaultValueSql: "GETUTCDATE()"),
        Name = table.Column<string>(nullable: false),
        UpdateDateTimeUtc = table.Column<DateTime>(nullable: false, defaultValueSql: "GETUTCDATE()")
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Person", x => x.Id);
    });

// Add the following:   
migrationBuilder.Sql(
@"CREATE TRIGGER [dbo].[Person_UPDATE] ON [dbo].[Person]
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;

    DECLARE @Id INT

    SELECT @Id = INSERTED.Id
    FROM INSERTED

    UPDATE dbo.Person
    SET UpdateDateTimeUtc = GETUTCDATE()
    WHERE Id = @Id
END");