UPDATING - CURRENT_TIMESTAMP column unexpected 0001-01-01 00:00:00

[TheTrigger]

The issue

When update a row, the createdAt column is set to 0001-01-01 00:00:00 instead of keep his value.

Steps to reproduce

Code

using (var context = new PromotersContext())
{
	var salePoint = new SalePoints()
	{
		Id = param_salepoint.Id,
		Name = param_salepoint.Name,
		// other properties
		// i'm not updating createdAt property ofc
	}

	var entity = context.SalePoints.Update(salePoint).Entity;
	context.SaveChanges();

	//entity.CreatedAt is 0001-01-01 00:00:00
}

DB

CREATE TABLE `sale_points` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`region_id` INT(10) UNSIGNED NOT NULL,
	`tipology_id` INT(10) UNSIGNED NOT NULL,
	`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

Really bad workaround

DateTime createAt;
using (var context = new PromotersContext())
{
	createAt = context.SalePoints.Where(s => s.Id == id).First().CreatedAt;
}

Further technical details

MySQL version: 5.7.20 Operating system: Windows 10 Pro Pomelo.EntityFrameworkCore.MySql version: 2.1.4

Other details about my project setup:

Scaffolding: Scaffold-DbContext "host=localhost;port=3306;database=promoters;username=root;password=" -Provider Pomelo.EntityFrameworkCore.MySql -OutputDir DbModels -Context PromotersContext -verbose -force

[wiredbarb]

The newly created object's properties have default values differing from those in the database, so they will be updated. Try fetching the SalePoint you want to update first:

var salePoint = context.SalePoints.Where(x => x.Id == param_salepoint.Id).Single();
salePoint.Name = param_salepoint.Name;
context.SaveChanges();

[crozone]

The correct way to fix this is to change the before and after save behaviour.

builder.Entity<SomeEntity>().Property(d => d.CreatedAt).Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;

builder.Entity<SomeEntity>().Property(d => d.CreatedAt).Metadata.AfterSaveBehavior= PropertySaveBehavior.Ignore;

BeforeSaveBehavior will prevent EF from specifying a value for the row on INSERT.

AfterSaveBehavior will prevent EF from specifying a value for the row on UPDATE.

If you want database driven add and update rows from the migration engine, you can also use .ValueGeneratedOnAdd() (for CreatedAt) and ValueGeneratedOnAddOrUpdate() (for UpdatedAt)

builder.Entity<SomeEntity>().Property(d => d.CreatedAt).ValueGeneratedOnAdd();

builder.Entity<SomeEntity>().Property(d => d.UpdatedAt).ValueGeneratedOnAddOrUpdate();

[1zerro1]

@crozone , Your solution works great, we use .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore); and .Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore; on a column that gets its value set by CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP­.

We are now facing a scenario where, in some update, we don't want the UPDATE CURRENT_TIMESTAMP to trigger. In other words, we want to explicitly pass the datetime to the update statement. Can't find any work around. Do you have any idea?

---

Pomelo.EntityFrameworkCore.MySql version 7.0.0 efcore 7

[crozone]

@raphael-theriault I'm glad the solution works!

We are now facing a scenario where, in some update, we don't want the UPDATE CURRENT_TIMESTAMP to trigger. In other words, we want to explicitly pass the datetime to the update statement. Can't find any work around. Do you have any idea?

Try setting the PropertySaveBehavior.Save behaviour again:

builder.Entity<Entity>().Property(e => e.UpdatedTime).ValueGeneratedOnAddOrUpdate();

builder.Entity<Entity>().Property(e => e.UpdatedTime).Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Save);
builder.Entity<Entity>().Property(e => e.UpdatedTime).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Save);

When the DateTime (or DateTimeOffset) property is unset (and it remains at the default value), the change tracker should detect the property as Unset and EF should not generate an UPDATE for the column, allowing the database to generate it using the default value. To override the value, simply set the property.

If you have issues with the change tracker mis-detecting changes, you can change the mode that it operates in by implementing INotifyPropertyChanged on the Entity and then setting the ChangeTrackingStrategy to ChangedNotifications):

 builder.Entity<Entity>().HasChangeTrackingStrategy(ChangeTrackingStrategy.ChangedNotifications);

I've also noticed that on SQLite, the database provider doesn't set a default value, so you can also pass the default SQL in for if the migration isn't setting up the default value correctly:

builder.Entity<Entity>().Property(e => e.UpdatedTime).HasDefaultValueSql("CURRENT_TIMESTAMP");

Hopefully some of this is helpful!

[1zerro1]

@crozone , thank you for your quick answer. Correct me if I am wrong but accessing ModelBuilder outside of context, at creation, is impossible. Let met clarify my scenario so I am sure we are on the same page.

99% of the time we want MySQL to set the value of UpdatedTime. So the global solution works great because the update statement does not include UpdatedTime columns in any statement.

For the other 1%, we want the update statement to pass a value in UpdatedTime so MySQL will not trigger the "ON UPDATE CURRENT_TIMESTAMP" and use our value instead.

The reason why I think your solution is not working for us, or maybe I am missing something, is that the context is already created when we want to update the value. So we can't user ModelBuilder to change anything, as we would in protected override void OnModelCreating(ModelBuilder modelBuilder).

In the end we have created a second DbContext and use it when our value needs to be set by the statement. But, in my mind, it's not optimal. I would like to be able to add the "UpdatedTime = OurValue" to the update statement on demand.

What's your tought on that?

[crozone]

@raphael-theriault I think I understand the issue.

The code above should be run in the DbContext during instantiation.

builder.Entity<Entity>().Property(e => e.UpdatedTime).ValueGeneratedOnAddOrUpdate();

builder.Entity<Entity>().Property(e => e.UpdatedTime).Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Save);
builder.Entity<Entity>().Property(e => e.UpdatedTime).Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Save);

This changes the behaviour of the UpdatedTime property globally on the dbcontext to save the value provided to it in the entity.

In previous versions of Pomelo and EF there was an issue where this would cause the column to always be saved, even if the property was unset. Hence the PropertySaveBehavior.Ignore value was used as a "nuclear option" to prevent the property from ever being recognised as being set, so CURRENT_TIMESTAMP would be used.

However with PropertySaveBehavior.Save, it should change the behaviour such that when the property is unset, the ON UPDATE CURRENT_TIMESTAMP is used (because the column is omitted from the UPDATE statement). When the property is set, it should be overridden. This relies on the change tracker correctly identifying that the property has or has not been set, by default it does this using the Snapshot strategy which checks if a property is equal to either the same value as before, or its default value . If this is inadequate, the ChangedNotifications strategy can be used with the INotifyPropertyChanged interface implemented on the entity to explicitly notify the change tracker that the property has changed. This would be required if you wanted to "maintain" the updated times current value by manually setting the property to itself, which would not work with the Snapshot strategy.

[1zerro1]

@crozone , will try the INotifyPropertyChanged for sure in a close future, and I'll make sure to post the answer.

As for the propertySaveBehavior.Save, it's not working. But I am not sure what you mean by "the property is unset". I have tried to change our Datetime to a Datetime? and just letting the property to null, but it throws "Column 'UpdatedAt' cannot be null"

Which makes sense because when PropertySaveBehavior.Save, then the update statement includes ``UpdatedAt = @p8 where @p8 is null.

As of today, our scenario "works" because we have two different projects that use the same context and have different needs regarding the UpdatedAt column. One needs to let the database manage the column, while the other needs to keep the existing value intact. We have moved the code .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore); in an interface only used by the projects that need it.

I can see the day that we will make an exception, and will definitely need to implement a better solution ( INotifyPropertyChanged is our best solution )

[Jellyfish5]

Hi