C# Pomelo mySQL trigger onUpdate Timestamp

Question

I'm trying to create a log table in Pomelo.MySQL which has an onUpdate Timestamp, but I can't seem to trigger it with Entity Framework.

This is my model for the table

public class OrganisationLog
{
    [Key]
    public int Id { get; set; }

    [Column(TypeName = "VARCHAR(1024)")]
    [Required(AllowEmptyStrings = false)]
    public string MachineName { get; set; }

    [DefaultValue("CURRENT_TIMESTAMP")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime LastContact { get; set; }

    public int OrganisationId { get; set; }

    [ForeignKey("OrganisationId")]
    public Organisation Organisation { get; set; }
}

And below is the function that should work.

private void UpdateOrganisationLog(Organisation organisation, string machineName)
{
    try
    {
        OrganisationLog organisationLog = _context.OrganisationLogs
            .Where(x => x.OrganisationId == organisation.Id && x.MachineName == machineName)
            .FirstOrDefault();

        if (organisationLog == null)
        {
            organisationLog = new OrganisationLog()
            {
                MachineName = machineName,
                OrganisationId = organisation.Id,
                LastContact = DateTime.Now
            };
            _context.OrganisationLogs.Add(organisationLog);
        }
        else
        {
            _context.Update(organisationLog);
        }
        _context.SaveChanges();
    }
    catch (Exception e)
    {
        Console.WriteLine("Error " + e.Message);
    }
}

I ended up making it work with a manual SQL statement, but I want to figure it out through Entity Framework.

_context.Database.ExecuteSqlCommand($"UPDATE organisationlogs SET LastContact = CURRENT_TIMESTAMP(6) WHERE Id = {organisationLog.Id}");

Could it have something to do with CURRENT_TIMESTAMP(6) rather than CURRENT_TIMESTAMP()? Not sure why Entity Framework has made it as (6).

Answer 1

According to the EF Core docs on Default Values, data annotations are not supported:

You can not set a default value using Data Annotations.

If it would have been supported by EF Core, than using it for CURRENT_TIMESTAMP would probably still not have worked, because it is not a System.DateTime value, but technically a SQL fragment.

---

In your case, a FluentAPI configuration like the following, that uses .HasDefaultValueSql() to specify the SQL fragment, should work for Pomelo 3.0.1+:

class MyContext : DbContext
{
    // ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrganisationLog>(entity =>
        {
            entity.Property(e => e.LastContact)
                .HasDefaultValueSql("CURRENT_TIMESTAMP");
        });
    }
}

The DatabaseGenerated(DatabaseGeneratedOption.Computed) attribute should not be necessary.

---

If you want to have the value not just generated on creation, but also updated automatically when changing the table row, use the following model definition instead:

class MyContext : DbContext
{
    // ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrganisationLog>(entity =>
        {
            entity.Property(e => e.LastContact)
                .ValueGeneratedOnAddOrUpdate();
        });
    }
}

---

In case you only want the value to be updated when changing the table row, but not when creating it, you can use ValueGeneratedOnUpdate() with EF Core 3.1.0.

There is a bug in EF Core < 3.1.0, where ValueGeneratedOnUpdate() will not generate correct C# code. This should not be an issue for most people, because lifetime support for EF Core 3.0.0 is very limited anyway (and as mentioned above, the feature is only supported by Pomelo since 3.0.1). If you need a workaround for 3.0.1 >= Pomelo < 3.1.0 anyway, then using ValueGeneratedOnAddOrUpdate() instead will work for most use cases.

---

See #959 on our GitHub repo for the fix that implemented support for datetime columns in conjunction with CURRENT_TIMESTAMP and for further details.

---

I'm using Pomelo.EntityFramework.MySql version 2.1.4. I'm hosting this with Elastic Beanstalk so I need to use an older version of dotnet

Everything above is not going to work correctly for Pomelo 2.1.4 (using a timestamp or timestamp(6) column might work, but you would need to manually change the DEFAULT statement to remove the single quotes, in case you scaffold the database). But you can always just change the table definition as a workaround.

If you are using migrations, the following line (or something similar) can be added to an Up() method for example:

migrationBuilder.Sql("ALTER TABLE `OrganisationLog` CHANGE COLUMN `LastContact` datetime(6) CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;");

Not ideal, but it should do its job for older Pomelo versions.