RowVersion and TimeStamp columns

Choosing the right entity configuration for the columns is essential. Here I show you the source tables, and the EF Core 6 configuration and the generated SQL to determine the correct answer.

Tables

CREATE TABLE VersionedNullable
(
    Id INT NOT NULL IDENTITY(1, 1),
    [Version] ROWVERSION NULL,
    Number INT NOT NULL,
    CONSTRAINT PK_VersionedNullable PRIMARY KEY (Id)
);

CREATE TABLE Versioned
(
    Id INT NOT NULL IDENTITY(1, 1),
    [Version] ROWVERSION NOT NULL,
    Number INT NOT NULL,
    CONSTRAINT PK_Versioned PRIMARY KEY (Id)
);

CREATE TABLE TimestampNotNull
(
    Id INT NOT NULL IDENTITY(1, 1),
    [Version] TIMESTAMP NOT NULL,
    Number INT NOT NULL,
    CONSTRAINT PK_TimestampNotNull PRIMARY KEY (Id)
);

CREATE TABLE TimestampNullable
(
    Id INT NOT NULL IDENTITY(1, 1),
    [Version] TIMESTAMP NULL,
    Number INT NOT NULL,
    CONSTRAINT PK_TTimestampNullable PRIMARY KEY (Id)
);

When executed, SQL Server converts the ROWVERSION to a TIMESTAMP. However, to be thorough I will test all scenarios.

Entity Configuration

In each test, the configuration was changed to include a combination of the following:

.ValueGeneratedOnAddOrUpdate()
.IsRowVersion()
.IsConcurrencyToken()

Column configuration

// VersionedNullable
builder.Property(x => x.Version)
       .HasColumnName(@"Version")
       .HasColumnType("timestamp")
       .IsRequired(false);

// Versioned
builder.Property(x => x.Version)
       .HasColumnName(@"Version")
       .HasColumnType("timestamp(8)")
       .IsRequired()
       .IsFixedLength()
       .HasMaxLength(8);

// TimestampNullable
builder.Property(x => x.Version)
       .HasColumnName(@"Version")
       .HasColumnType("timestamp")
       .IsRequired(false);

// TimestampNotNull
builder.Property(x => x.Version)
       .HasColumnName(@"Version")
       .HasColumnType("timestamp(8)")
       .IsRequired()
       .IsFixedLength()
       .HasMaxLength(8);

Inserting a row

A row was added and .SaveChanges() was called. Here is what worked (it inserted the row) and what didn't (threw an exception):

Column type	.ValueGeneratedOnAddOrUpdate	.IsRowVersion	.IsConcurrencyToken	INSERT worked
ROWVERSION NOT NULL				No
ROWVERSION NOT NULL			x	No
ROWVERSION NOT NULL		x		Yes
ROWVERSION NOT NULL		x	x	Yes
ROWVERSION NOT NULL	x			Yes
ROWVERSION NOT NULL	x		x	Yes
ROWVERSION NOT NULL	x	x		Yes
ROWVERSION NOT NULL	x		x	Yes
ROWVERSION NOT NULL	x	x	x	Yes
ROWVERSION NULL				No
ROWVERSION NULL			x	No
ROWVERSION NULL		x		Yes
ROWVERSION NULL		x	x	Yes
ROWVERSION NULL	x			Yes
ROWVERSION NULL	x		x	Yes
ROWVERSION NULL	x	x		Yes
ROWVERSION NULL	x		x	Yes
ROWVERSION NULL	x	x	x	Yes
TIMESTAMP NULL				No
TIMESTAMP NULL			x	No
TIMESTAMP NULL		x		Yes
TIMESTAMP NULL		x	x	Yes
TIMESTAMP NULL	x			Yes
TIMESTAMP NULL	x		x	Yes
TIMESTAMP NULL	x	x		Yes
TIMESTAMP NULL	x		x	Yes
TIMESTAMP NULL	x	x	x	Yes
TIMESTAMP NOT NULL				No
TIMESTAMP NOT NULL			x	No
TIMESTAMP NOT NULL		x		Yes
TIMESTAMP NOT NULL		x	x	Yes
TIMESTAMP NOT NULL	x			Yes
TIMESTAMP NOT NULL	x		x	Yes
TIMESTAMP NOT NULL	x			Yes
TIMESTAMP NOT NULL	x		x	Yes
TIMESTAMP NOT NULL	x	x	x	Yes

What SQL is generated

A row was updated and the following SQL was generated

TIMESTAMP

NULL or NOT NULL does not matter as the same SQL is generated.

No additional properties

SET NOCOUNT ON;
UPDATE [dbo].[TimestampNullable] SET [Number] = 4
WHERE [Id] = 9;
SELECT @@ROWCOUNT;

.ValueGeneratedOnAddOrUpdate()

SET NOCOUNT ON;
UPDATE [dbo].[TimestampNullable] SET [Number] = 5
WHERE [Id] = 9;
SELECT [Version]
FROM [dbo].[TimestampNullable]
WHERE @@ROWCOUNT = 1 AND [Id] = 9;

.IsRowVersion()

SET NOCOUNT ON;
UPDATE [dbo].[TimestampNullable] SET [Number] = 6
WHERE [Id] = 9 AND [Version] = 0x0000000000004681;
SELECT [Version]
FROM [dbo].[TimestampNullable]
WHERE @@ROWCOUNT = 1 AND [Id] = 9;

.IsConcurrencyToken()

SET NOCOUNT ON;
UPDATE [dbo].[TimestampNullable] SET [Number] = 7
WHERE [Id] = 9 AND [Version] = 0x0000000000004682;
SELECT @@ROWCOUNT;

.IsRowVersion().IsConcurrencyToken()

SET NOCOUNT ON;
UPDATE [dbo].[TimestampNullable] SET [Number] = 8
WHERE [Id] = 9 AND [Version] = 0x0000000000004683;
SELECT [Version]
FROM [dbo].[TimestampNullable]
WHERE @@ROWCOUNT = 1 AND [Id] = 9;

Conclusion

• .ValueGeneratedOnAddOrUpdate() is not the right option to choose because it does not check the version during an update. However, it allows a row to be inserted.
• .IsRowVersion() works correctly for both ROWVERSION and TIMESTAMP columns types.
• .IsConcurrencyToken() works for the insert, but does not read back the latest value for the ROWVERSION / TIMESTAMP.

The answer is to use a both .IsRowVersion() and .IsConcurrencyToken() in all cases:

• Column type is a ROWVERSION or a TIMESTAMP
• Column is NULL or NOT NULL