Wednesday, July 21, 2010

Compare binary data in Linq 2 SQL

I just ran into an interesting problem using Linq 2 SQL. I wanted to compare binary data directly on the server, that means that i wanted a linq query that would translate to sql and perform comparison over a binary column. This isn't as easy as you would expect when using Linq 2 SQL. What i usually ended up with was a mess, either writing my own sql code or just grabbing the entire table in memory and using the SequenceEquals extension method on the binary data.
Now i've found there's a much better way. Although this looks a lot like a hack, the result is so amazing that i encourage it to be used as a standard.
Apparently this guy just dug deep into the linq 2 sql code, and what he ended up with was this:
private static bool IsCompareMethod(SqlMethodCall call)
{
    return (((call.Method.IsStatic && 
             (call.Method.Name == "Compare"))&&
             (call.Arguments.Count > 1)) && 
             (call.Method.ReturnType == typeof(int)));
}
So basically if we provide a static method named 'Compare' which has two parameters and returns an int, then it is a comparison method. This means it will be transformed into a comparison expression inside SQL. Using this information, he suggests just creating an extension method to the Binary (in my case, byte[] data, but it's basically the same thing) method, with the proper signature.
public static class ComparatorBinar
{
    public static int Compare(this byte[] b1, byte[] b2)
    {
        throw new NotImplementedException();
    }
}
We don't need to throw an exception here, but it's just to suggest that this method isn't actually going to be called from inside .net, but it will be mapped to a comparison method in SQL.
We can now use this in any linq query to compare binary data:
var data = GetSomeBinaryData();
var elem = (from e in dataContext.Elements
            where 
            e.BinaryData.Compare(data) == 0
            select e).FirstOrDefault();
Voila! This of course will be translated into T-SQL, which knows by default to compare binary data.
I'm not sure why there isn't a default implementation for comparing binary data btw.
Original source: Linq 2 SQL, Expression Trees and Binary Comparing

No comments:

Post a Comment