5

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?

Bas
  • 1,946
  • 21
  • 38
  • 2
    Why are you using a byte array instead of a DateTime? – user247702 Dec 03 '13 at 09:24
  • 2
    Because that's how TimeStampAttribute works. "A row version type (also known as a sequence number) is a binary number that is guaranteed to be unique in the database. It does not represent an actual time. Row version data is not visually meaningful." (http://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.timestampattribute(v=vs.110).aspx) – Bas Dec 03 '13 at 09:29

2 Answers2

14

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)
Edward Brey
  • 40,302
  • 20
  • 199
  • 253
  • 1
    If you need your code to be portable to big endian machines, you can make the calls to `Reverse` conditional so that they are omitted if `BitConverter.IsLittleEndian` is false. – Edward Brey Jan 21 '15 at 15:55
3

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

Community
  • 1
  • 1
phil soady
  • 11,043
  • 5
  • 50
  • 95