How to compare two byte arrays with greater than or less than operator value in C# or Linq?

Question

I have a byte array in my Code First Entity Framework for SQL TimeStamps, mapping as given below:

 [Column(TypeName = "timestamp")]
 [MaxLength(8)]
 [Timestamp]
 public byte[] TimeStamps { get; set; }

The above property is equal to SQL server "timestamp" Data type in C#.

In SQL server I can compare "timestamp" easily as below...

SELECT * FROM tableName WHERE timestampsColumnName > 0x000000000017C1A2

Same thing I want to achieve in C# or Linq Query. Here I have written my Linq query, which is not working properly.

 byte[] lastTimeStamp = someByteArrayValue;
 lstCostCenter.Where(p => p.TimeStamps > lastTimeStamp);

I have also tried with BitConverter to compare a two byte array which is also not working...

 lstCostCenter.Where(p => BitConverter.ToInt64(p.TimeStamps, 0) > BitConverter.ToInt64(lastTimeStamp, 0));

How can I compare byte arrays in C# or Linq Query.

Note - I just do not want to compare two arrays normally like using SequenceEqual or any other methods which are just compare and return true or false. I want the comparison in Linq query with Greater than > or Less than < operator which gives proper data like SQL Server query.

Answer 1

One way is to use IStructuralComparable, which Array implicitly implements:

byte[] rv1 = new byte[] { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x06, 0x01 };
byte[] rv2 = new byte[] { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x06, 0x05 };

var result = ((IStructuralComparable)rv1).CompareTo(rv2, Comparer<byte>.Default); // returns negative value, because rv1 < rv2

If for some reason you want to use BitConverter, you have to reverse arrays, because BitConverter is little endian on most architectures (to be safe - you should check BitConverter.IsLittleEndian field and reverse only if it returns true). Note that it's not very efficient to do this.

var i1 = BitConverter.ToUInt64(rv1.Reverse().ToArray(), 0);
var i2 = BitConverter.ToUInt64(rv2.Reverse().ToArray(), 0);

Now if you use Entity Framework and need to compare timestamps in database query, situation is a bit different, because Entity Framework will inspect your query expression looking for patterns it understands. It does not understand IStructuralComparable comparisions (and BitConverter conversions too of course), so you have to use a trick. Declare extension method for byte array with the name Compare:

static class ArrayExtensions {
    public static int Compare(this byte[] b1, byte[] b2) {
        // you can as well just throw NotImplementedException here, EF will not call this method directly
        if (b1 == null && b2 == null)
            return 0;
        else if (b1 == null)
            return -1;
        else if (b2 == null)
            return 1;
        return ((IStructuralComparable) b1).CompareTo(b2, Comparer<byte>.Default);
    }
}

And use that in EF LINQ query:

var result = ctx.TestTables.Where(c => c.RowVersion.Compare(rv1) > 0).ToList();

When analyzing, EF will see method with name Compare and compatible signature and will translate that into correct sql query (select * from Table where RowVersion > @yourVersion)