SQL Server Timestamp column crash on update

[wezzix]

I have a table that I'm trying to update that contains a timestamp column. The timestamp column is marked DatabaseGenerated (DatabaseGeneratedOption.Computed). I would expect the entity to be updated in DB, and the system would retrieve the new value of the timestamp column. However, updating the entity gives the following error: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column. This is due to the generated code that creates a temp table from the existing table which then has datatype timestamp, and then trying to INSERT into this timestamp column, which is not allowed. You would have to have a binary(8) column for that to work. Would it be hard to simply do SELECT Timestamp FROM Mytable WHERE Id = @Id at the end of the update?

[wezzix]

I came up with a fix. In SQL Server you can simply use OUTPUT clause without INTO and this should resolve the issue, unless you have a particular need for the temp table. Source

Here are my workarounds which could serve as a fix:

var sql = builder.Format($@"
INSERT INTO {nameof(MyTable):T} ({builder.ConstructColumnEnumerationForInsert()})
OUTPUT inserted.{nameof(MyTable.Id):C},inserted.{nameof(PickProcess.MyTable):C}
VALUES ({builder.ConstructParamEnumerationForInsert()})");

var sql = builder.Format($@"
UPDATE {nameof(MyTable):T} SET {builder.ConstructUpdateClause()}
OUTPUT inserted.{nameof(MyTable.Timestamp):C}
WHERE {builder.ConstructKeysWhereClause()}");

The absense of a temp table and OUTPUT INTO means that Timestamp now can be supported.

[MoonStorm]

That insert into the temp table is indeed the problem for a timestamp/rowversion column, however the fix is not trivial. Removing that temp table and relying fully on the OUTPUT clause alone will ignore any triggers placed on the table.

[MoonStorm]

We just added support for this in 3.1 and added tests with combined scenarios of rowversion columns and triggers.