How to compare SQL Server CDC LSN values in C#?

Question

In SQL its easy as it supports the binary(10) LSN values for comparison:

SELECT *, __$start_lsn, __$seqval
FROM cdc.fn_cdc_get_all_changes_dbo_sometable(@startLsn, @endLsn, 'all update old') 
WHERE __$seqval > @seqval 
ORDER BY __$start_lsn, __$seqval

In C# it's more difficult:

byte[] mySeqval = ...
foreach(var row in cdcData)
{
    if(row.seqval > mySeqval) // Cannot perform this
        ...
}

Can the LSN / SeqVal values be converted into a number than can be compared easily? These are 10 bytes (80 bits) in size.

My project is in .Net 3.5

Answer 1

Inspired by @casperOne's fixed length solution here https://stackoverflow.com/a/10658931/8478013 I created an extension method that allows you to order by byte array like this:

allchanges.OrderByDescendingFixedLength(sortLength: 10, x => x.___seqval)

here's the extension method:

public static IEnumerable<T> OrderByFixedLength<T>(this IEnumerable<T> items, int sortLength, Func<T, byte[]> fieldValue)
    {
        //this routine came from:
        //      https://stackoverflow.com/questions/10658709/linq-orderbybyte-values
        //  it was modified to be generic <T> instead of specific type

        // Validate parameters.
        if (items == null) throw new ArgumentNullException("items");
        if (sortLength < 0) throw
            new ArgumentOutOfRangeException("sortLength", sortLength,
                "The sortLength parameter must be a non-negative value.");

        // Shortcut, if sortLength is zero, return the sequence, as-is.
        if (sortLength == 0) return items;

        // The ordered enumerable.
        IOrderedEnumerable<T> ordered = items.OrderBy(x => fieldValue(x)[0]);

        // Cycle from the second index on.
        for (int index = 1; index < sortLength; index++)
        {
            // Copy the index.
            int indexCopy = index;

            // Sort by the next item in the array.
            ordered = ordered.ThenBy(x => fieldValue(x)[indexCopy]);
        }

        // Return the ordered enumerable.
        return ordered;
    }

  public static IEnumerable<T> OrderByDescendingFixedLength<T>(this IEnumerable<T> items, int sortLength, Func<T, byte[]> fieldValue)
    {
        //we could probably optimize this, but honestly it's used so little and already quite quick... so we'll just go with it
        return items.OrderByFixedLength(sortLength, fieldValue).Reverse();
    }

Answer 2

I was surprised not to find a half-decent answer to this problem anywhere when I looked over the last few weeks.

The main problem with LSNs is that they are 10 bytes, so they can't simply be converted to Int64 and compared (aside: will you really generate that many LSNs?! Int64 is really big). And as the OP discovered, comparing the bytes one-by-one is a bit painful/error prone (comparing for equality is fine - comparing for greater-than/less-than less so). However, as of .Net Framework 4 we have the BigInteger class, which can be used to easily compare integers exceeding 8 bytes.

So the problem is just how to get the varbinary(10) from a LSN into a BigInteger. From inspection[1] it appears that SQL stores the LSN in big-endian format, so you have to:

• get the varbinary(10) into memory. LinqToSql will give you Binary, other providers will map to byte[] directly.
• flip the bytes, if you are on little-endian architecture (hint: you are). IEnumerable.Reverse().ToArray() will do that if you don't want to do a reverse-loop yourself
• call new BigInteger(bytes)
• compare the values at your leisure

This might look something like this:

// https://gist.github.com/piers7/91141f39715a2ec133e5
// Example of how to interpret SQL server CDC LSNs in C# / .Net
// This is required when polling a server for updates in order to determine
// if a previously stored LSN is still valid (ie > min LSN available)

// Requires .Net 4 (or equivilent BigInteger implementation)
// Sample is a Linqpad script, but you get the idea

// NB: That SQL uses big-endian representation for it's LSNs is not
// (as best I know) something they guarantee not to change

Connection.Open();
var command = Connection.CreateCommand();
command.CommandText = @"select sys.fn_cdc_get_max_lsn() as maxLsn";
var bytes = (byte[])command.ExecuteScalar();

// dump bytes as hex
var hexString = string.Join(" ", bytes.Select(b => b.ToString("X2")))
    .Dump("Hex String");

if(BitConverter.IsLittleEndian)
    bytes = bytes.Reverse().ToArray();

var bigInt = new BigInteger(bytes)
    // dump Integer representation
    .Dump("Big Integer")
;

[1] I made sequential changes, and looked at the LSNs. The last byte was clearly incrementing, hence big-endian.

Answer 3

Didn't need to use any of the above in the end. Ony of my collegues solved the problem in the end (Thanks Tony Broodie). The way to do this was to compare to seqval, then take+1. Simples.

SqlExecutor.ExecuteReader(cnn,
string.Format("SELECT {0} , __$start_lsn, __$seqval , __$update_mask " +
    "FROM cdc.fn_cdc_get_all_changes_{1}(@startLsn,@endLsn,'all update old') cdc {2} " +
    "where __$operation = {3} ORDER BY __$start_lsn, __$seqval", columns,
    captureInstance, joins, (int)operation), 
    reader =>
    {
        if (reader != null)
            items.Add(createEntity(reader));
    }, 5, 60, new SqlParameter("@startLsn", lsn), 
              new SqlParameter("@endLsn", endLsn));
});
startLsn = lsn;
seqVal = sequence;
var startIndex = sequence == null ? 0 : 
  items.FindIndex(0, item => item.Lsn.SequenceEqual(lsn)
    && item.Seqval.SequenceEqual(sequence)) + 1; // <---- Look here. See the +1
return items.Skip(startIndex).ToList();

Answer 4

Wrote my own LSN comparer in the end:

public class CdcLsnValue : IEquatable<CdcLsnValue>
{
    public byte[] Bytes;
    private const int Size = 10;

    public CdcLsnValue()
    {
        Bytes = null;
    }

    public CdcLsnValue(byte[] lsn)
    {
        if (lsn == null)
        {
            Bytes = null;
            return;
        }
        if(lsn.Length != Size)
            throw new ArgumentOutOfRangeException("lsn");
        Bytes = (byte[]) lsn.Clone();
    }

    public static bool operator ==(CdcLsnValue left, CdcLsnValue right)
    {
        if (ReferenceEquals(left, right)) return true;
        if (ReferenceEquals(null, left)) return false;
        if (ReferenceEquals(null, right)) return false;

        for (int i = 0; i < Size; i++)
        {
            if (left.Bytes[i] == right.Bytes[i])
                continue;
            return false;
        }
        return true;

    }

    public static bool operator !=(CdcLsnValue left, CdcLsnValue right)
    {
        return !(left == right);
    }

    public static bool operator <=(CdcLsnValue left, CdcLsnValue right)
    {
        if (ReferenceEquals(null, left)) return false;
        if (ReferenceEquals(null, right)) return false;

        for (int i = 0; i < Size; i++)
        {
            if (left.Bytes[i] <= right.Bytes[i])
                continue;
            return false;
        }
        return true;
    }

    public static bool operator >=(CdcLsnValue left, CdcLsnValue right)
    {
        if (ReferenceEquals(null, left)) return false;
        if (ReferenceEquals(null, right)) return false;

        for (int i = 0; i < Size; i++)
        {
            if (left.Bytes[i] >= right.Bytes[i])
                continue;
            return false;
        }
        return true;
    }

    public static bool operator <(CdcLsnValue left, CdcLsnValue right)
    {
        if (ReferenceEquals(null, left)) return false;
        if (ReferenceEquals(null, right)) return false;

        if (left == right)
            return false;

        return left <= right;
    }

    public static bool operator >(CdcLsnValue left, CdcLsnValue right)
    {
        return !(left < right);
    }

    public bool Equals(CdcLsnValue other)
    {
        if (ReferenceEquals(null, other)) return false;
        if (ReferenceEquals(this, other)) return true;
        return Equals(other.Bytes, Bytes);
    }

    public override bool Equals(object obj)
    {
        if (ReferenceEquals(null, obj)) return false;
        if (ReferenceEquals(this, obj)) return true;
        if (obj.GetType() != typeof(CdcLsnValue)) return false;
        return Equals((CdcLsnValue)obj);
    }

    public override int GetHashCode()
    {
        return (Bytes != null ? Bytes.GetHashCode() : 0);
    }
}

Answer 5

Currently investigating http://intx.codeplex.com/ as its .Net 2.0