Improve ergonomics of Timestamp columns in SQLite

[asbjornu]

As summarised in #29916 and this Stack Overflow question (with answers), the ergonomics of using [Timestamp] columns with EF Core in SQLite isn't the best. There are several problems that each have to be tackled at different angles.

1. SQLite doesn't have a TIMESTAMP or ROWVERSION column type, so values are stored in TEXT columns by default. This makes it necessary to serialize the byte[] into a string, which EF Core's SQLite provider doesn't do automatically.
2. With a custom ValueConverter and ValueComparer configured, EF Core's SQLite provider doesn't ensure that the value for the Timestamp column is provided. This makes it necessary to manually configure a default value with something like timestampProperty.SetDefaultValueSql("CURRENT_TIMESTAMP").
3. With the default value configured to CURRENT_TIMESTAMP, values are provided on INSERT, but not on UPDATE. To fix that, a trigger must be created in SQLite.
4. Creating an AFTER UPDATE trigger doesn't work because, as detailed in SQLite: RETURNING clause doesn't work with AFTER triggers #29811, SQLite's RETURNING doesn't include values changed by AFTER UPDATE triggers. The trigger therefore needs to be created as BEFORE UPDATE.
5. By now, you'll realize CURRENT_TIMESTAMP isn't a suitable value for Timestamp columns, because it serializes to a date value with only second precision. To overcome this, CURRENT_TIMESTAMP in both the default value and BEFORE UPDATE trigger must be replaced with something like STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') to get millisecond precision.
6. Even millisecond precision isn't always enough and neither a date nor its storage in a TEXT column is actually ideal for Timestamp columns. Storing something like RANDOMBLOB(8) in a BLOB or REAL column would be much better, and something EF Core's SQLite provider should do out of the box.

I've tried to amend my solution to use RANDOMBLOB(8) values stored in BLOB column types for Timestamp without getting it to work. I'm partly content with the STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') value and TEXT column type solution, but it's a lot of custom code involved and it's not exactly a perfect solution.

I would therefore love to see EF Core's SQLite provider set up column types, default values, triggers, etc., for Timestamp columns automatically and preferably as RANDOMBLOB(8) values stored in BLOB columns – or at the very least, provide extension methods so setting them up would be much easier than it currently is.

[ajcvickers]

Likely covered by #2195.

[asbjornu]

Indeed, that would probably solve all of my quarrels, @ajcvickers. Closing as duplicate. Just out of curiosity: Is it possible to replicate the proposed behavior in EF Core 7? If so, how?