rowVersion gets mapped to byte[8] in Entityframe work but when manually casting it's byte[18]

Question

I am getting rowVersion from database as byte[8]

var rowVersion= new MyContext().Employee.FirstOrDefault(x => x.Id).rowVersion;
// suppose above the actual databse values is 0x0000000000038B8C
var rowVersionToLong = BitConverter.ToInt64(rowVersion,0);

Now if i manually do this:

String rowversionStr = "0x0000000000038B8C";
byte[] mybyteArray = System.Text.ASCIIEncoding.ASCII.GetBytes(rowversionStr);

This gives me byte[18] and when i convert it to Int64 it gives me different value.

i am not getting this.

I want to pass rowVersion as parameter to WebApi get method. As Byte[] is not allowed so i am passing it as a string

Update:

 public IHttpActionResult Get(string rowVersion, int id)
    {

        var exisitingRowVersion = long.Parse(rowVersion.Substring(2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);

        var result = new MyContext().employees.ToList().Where(x => x.Id == 2 && BitConverter.ToInt64(x.RowVersion, 0) > exisitingRowVersion);

        return Ok(result);

Not getting why this one doesn't work. We are basically comparing long with long

Answer 1

You have three major problems. It shouldn't be this complicated, but it is.

Here is the solution I use: Timestamp.cs. It's way easier. I'll put an example at the end.

1. Not comparing apples to apples

rowVersion is an 8-byte array. Each byte represents a section of a 64-bit integer and will be 0 - 255.

System.Text.ASCIIEncoding.ASCII.GetBytes encodes ASCII strings, not integers. It returns an 18-byte array. Each byte represents a textual character and will be '0' (48) - '9' (57), 'A' (65) - 'F' (70), or 'x' (120).

Solution: you're on the right track with long.Parse("0x0000000000038B8C".Substring(2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);

2. SQL Server timestamps are stored big-endian

BitConverter.ToUInt64 is either big-endian or little-endian, depending on whether you're running on a system where ulong is big-endian or little-ending. You can see this for yourself. You need a conversion that will always be big-endian regardless of the system you are running on:

static ulong BigEndianToUInt64(byte[] bigEndianBinary)
{
    return ((ulong)bigEndianBinary[0] << 56) |
           ((ulong)bigEndianBinary[1] << 48) |
           ((ulong)bigEndianBinary[2] << 40) |
           ((ulong)bigEndianBinary[3] << 32) |
           ((ulong)bigEndianBinary[4] << 24) |
           ((ulong)bigEndianBinary[5] << 16) |
           ((ulong)bigEndianBinary[6] <<  8) |
                   bigEndianBinary[7];
}

3. Binary comparisons are unsigned

When SQL Server compares 0x0FFFFFFFFFFFFFFF < 0xFFFFFFFFFFFFFFFF, 0xFFFFFFFFFFFFFFFF is bigger. To keep the same meaning that SQL Server treats it with, you must use ulong and not long. Otherwise 0xFFFFFFFFFFFFFFFF becomes -1L rather than what SQL Server considers it as, ulong.MaxValue.

Granted, 9 quadrillion things have to happen before the high bit of a timestamp column gets used, but you might use the same code to compare two binary(8) timestamps that were generated some other way. The important thing is to duplicate SQL Server's comparison behavior.

The cleanest solution

Here is the solution I use: Timestamp.cs.

Your code becomes:

var existingRowVersion = (Timestamp)ulong.Parse(rowVersion.Substring(2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);

var result = new MyContext().employees.ToList().Where(x => x.Id == 2 && (Timestamp)x.RowVersion > exisitingRowVersion);

Basically once you cast to Timestamp, you can't go wrong.

Sadly, no matter which method you use, there's no good way to apply this filter on the server-side instead of the client side. That's the subject of this question. Guess what I discovered! A way to do this with Entity Framework 6.1.3! How cool is that?

HOWEVER, and this is unrelated to your question, you should absolutely put the Id == 2 filter on the server side (before you call ToList). Otherwise you are transferring the entire table to your application and then discarding all but one row on the client side. You should do this:

var existingRowVersion = (Timestamp)ulong.Parse(rowVersion.Substring(2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);

var result = new MyContext().employees.Where(x => x.Id == 2).ToList().Where((Timestamp)x.RowVersion > exisitingRowVersion);

Best:

var existingRowVersion = (Timestamp)ulong.Parse(rowVersion.Substring(2), NumberStyles.HexNumber, CultureInfo.InvariantCulture);

var employee = new MyContext().employees.SingleOrDefault(x => x.Id == 2);
if (employee == null) ... // Deleted
else if ((Timestamp)employee.RowVersion > exisitingRowVersion) ... // Updated

Answer 2

Before, short story:

For me works to convert from "AAAxxx==" to byte array using Convert.FromBase64String and vice versa.

Long story:

In my case I get from SQL 2016 DB the output of complex object including RowVersion from a query that uses FOR JSON PATH and SQL transform RowVersion in string base 64 and dates to string and use that output direct as a result but when is used as request pay load as is in MVCx or WCF fails because they works well with rowversion transform like an output serialized from object to JSON like WCF or MVCx does: rowversion as byte array and date as /Date(###). In order to convert from output of JSON-SQL to JSON-MVC-WCF compatible I have to transform the response before the outout in the server using an extension that convert in this case for RowVersion with an example value of AAAxxx== to [0,0,0,#,#,#] and for that pass AAAxxx== as the parameter for Convert.FromBase64String and iterate the byte array to output the expected result.