Comparing EF timestamp values

Question

I've got an EF Code First model with a byte array field marked with the Timestamp attribute. I need to compare two timestamps with each other and determine which is newer. This seems straightforward but I'm unsure what sort of value SQL Server is filling that byte array with. Do I just convert them to UInt64 values, like so:

BitConverter.ToInt64(item1.Timestamp, 0) < BitConverter.ToInt64(item2.TimeStamp, 0)

...or am I walking into some subtle trap here?

Answer 1

Yes, you are walking into a trap. The byte array stores a rowversion in big endian format. However, BitConverter.ToInt64 expects a little endian format on x86 and x64 CPU architectures. I ran a simple test using BitConverter and got an initial rowversion of 0xd207000000000000 and the next rowversion of 0xd307000000000000. SQL Server is incrementing the last byte of the 8-byte sequence, but BitConverter thinks the first byte is most significant. It won't take many increments before your order comparisons stop working once in a while.

The solution is to reverse the order of the rowversion bytes, like this:

BitConverter.ToInt64(item1.Timestamp.Reverse().ToArray(), 0) <
BitConverter.ToInt64(item2.TimeStamp.Reverse().ToArray(), 0)

Answer 2

Rowversion is the correct type in SQL server. EF uses a ByteArray to map to that. Or better said. The attribute [Timestamp] or fluent API Property(x).IsRowVersion is mapped to SQL rowversion via byte Array.

So unless you need the actual date and time, then the ROWVERSION is the MS recommended approach. Sql Server Rowversion

Yes The values are an indication of relative time in a sense that a smaller value was Initiated prior. But if you are using dirty reads you need to consider the implications of a Greater than comparison.

Since it is 8 bytes, you wont be there when it runs out ;-)
So apart from dirty reads, you can compare them yes.

Related topic : What if rowversion rolls over