Retrieving Length of Varbinary Field in NHibernate Linq

I wanted to SELECT the length of a field declared as VARBINARY(MAX) and mapped to a C# byte[] property. So naïvely I wrote the query in NHibernate Linq as

session.Query<MyTable>().Where(...).Select(t => new { t.ID, t.Pdf.Length })

Rather unexpectedly, NHibernate decided to translate this Linq query into a sub-select in the form

select t0_.ID as col_0_0_,
  (select cast(count(*) as INT) from myTable t0_) as col_1_0_
from myTable t0_

clearly mistaking the .Length as a COUNT(), rather than a LEN().

RegisterGenerator to the rescue!

Having used NH generators already once or twice, I wrote an extension method

public static int? GetBytesLen(this byte[] array)
  return null;

a generator class

public class BinaryLengthMethodsHqlGenerator : BaseHqlGeneratorForMethod
  public BinaryLengthMethodsHqlGenerator()
    SupportedMethods = new[] {
      ReflectionHelper.GetMethodDefinition((byte[] x) => x.GetBytesLen())
  public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
    ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, 
    IHqlExpressionVisitor visitor)
    return treeBuilder.MethodCall("bytes_length", 

wired the new method into my registry class

internal class LinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
  public LinqToHqlGeneratorsRegistry()
        (byte[] x) => x.GetBytesLen()), 
        new BinaryLengthMethodsHqlGenerator());

which is itself registered in NH’s configuration


and registered the SQL equivalent of the declared pseudo-function “bytes_length” in my NH Dialect

internal class MsSql2008DialectFixed : MsSql2008Dialect
  protected override void RegisterFunctions()
      new SQLFunctionTemplate(NHibernateUtil.Int32, "len(?1)"));

Next, I modified the above Linq query to

session.Query<MyTable>().Where(...).Select(t => new { t.ID, t.Pdf.GetBytesLen() })

and NHibernate now generates the desired SQL query

select t0_.ID as col_0_0_,
  len(t0_.Pdf) as col_1_0_
from myTable t0_

As I wrote this article, I found that RegisterGenerator() not only supports methods, but also properties. But when I tried and adapted the code to use the byte array’s Length property, RegisterGenerator() threw the exception

Unable to cast object of type ‘System.Linq.Expressions.UnaryExpression’ to type ‘System.Linq.Expressions.MemberExpression’.

and it turned out that .Length is translated into a UnaryExpression of type ArrayLength, rather than a MemberExpression. This phenomenon is covered by at least 2 SO questions, here and here.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: