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 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.

What the generator produces

A rowversion column comes through as byte[]:

    // Document
    public class Document
    {
        public int DocumentId { get; set; } // DocumentId (Primary key)

        /// <summary>
        /// The title shown to users. Not the file name.
        /// </summary>
        public string Title { get; set; } // Title (length: 100)
        public byte[] RowVersion { get; set; } // RowVersion (length: 8)
        public int? ReviewedByUserId { get; set; } // ReviewedByUserId
    }

The rest of this page is about which EF configuration to pair it with, which is the part that is not obvious.

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