Change token [Timestamp] has no effect for SQLite

[akovac35]

I have a table with the following column:

[Timestamp]
public byte[]? OpCounter { get; set; }

When inserting a new row in the table, the object instance is not automatically updated - OpCounter value is null after save. Also, if I later query the data, the OpCounter value is still null. Similarly for updates.

To Reproduce

Simply run the GitHub code and review the failing test. Each test has Additional output containing EFCore logs etc. The working test suggests a solution - introduce triggers during EnsureCreated and in Migrations, similarly as per suggested solution.

Suggested solution

Create, update or delete triggers as needed during EnsureCreated and in Migrations:

Change-token-Timestamp-has-no-effect-for-SQLite

protected void InitializeTriggers(TestSqliteContext context)
{
	var tables = context.Model.GetEntityTypes();

	foreach (var table in tables)
	{
		var props = table.GetProperties()
		.Where(p => p.ClrType == typeof(byte[])
		&& p.ValueGenerated == Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate
		&& p.IsConcurrencyToken);

		var tableName = table.GetTableName();

		foreach (var field in props)
		{
			string[] SQLs = new string[] {
	$@"CREATE TRIGGER IF NOT EXISTS Set{tableName}_{field.Name}OnUpdate
	AFTER UPDATE ON [{tableName}] FOR EACH ROW
	BEGIN
		UPDATE [{tableName}]
		SET [{field.Name}] = randomblob(8)
		WHERE rowid = NEW.rowid;
	END
	",
	$@"CREATE TRIGGER IF NOT EXISTS Set{tableName}_{field.Name}OnInsert
	AFTER INSERT ON [{tableName}] FOR EACH ROW
	BEGIN
		UPDATE [{tableName}]
		SET [{field.Name}] = randomblob(8)
		WHERE rowid = NEW.rowid;
	END
	"
};

			foreach (var sql in SQLs)
			{
				using (var command = context.Database.GetDbConnection().CreateCommand())
				{
					command.CommandText = sql;
					command.ExecuteNonQuery();
				}
			}
		}
	}
}

Additional context

Microsoft.Data.Sqlite version:
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.7" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.1.7" />
Target framework:
<TargetFramework>netcoreapp3.1</TargetFramework>
Operating system:
Windows 10

[ajcvickers]

Duplicate of #2195