NHibernate String Comparison

If you try to compare strings in NHibernate Linq using string.Compare() or [string property].CompareTo(), the query evaluation will throw a NotSupportedException.

After a bit of searching I found various NHibernate extensions that add TSQL functionality to NH:

For the 4 string comparison operations (GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual) we define a C# extension method:

public static class StringComparison
  public static bool GreaterThan(this string s, string other)
    return string.Compare(s, other) > 0;

Next, we define a BaseHqlGeneratorForMethod class for each of these extension methods which generates the HqlTree for the new operation:

public class StringGreaterThanGenerator 
  : BaseHqlGeneratorForMethod
  public StringGreaterThanGenerator()
    SupportedMethods = new[]
       ReflectionHelper.GetMethodDefinition<string>(x => x.GreaterThan(null))

  public override HqlTreeNode BuildHql(MethodInfo method, 
    System.Linq.Expressions.Expression targetObject, 
    ReadOnlyCollection<System.Linq.Expressions.Expression> arguments, 
    HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    return treeBuilder.GreaterThan(

Finally, the extensions have to be registered by a registry helper class which is added to the NH configuration:

public class StringComparisonLinqtoHqlGeneratorsRegistry 
  : DefaultLinqToHqlGeneratorsRegistry
  public StringComparisonLinqtoHqlGeneratorsRegistry()
    this.Merge(new StringGreaterThanGenerator());
    this.Merge(new StringGreaterThanOrEqualGenerator());
    this.Merge(new StringLessThanGenerator());
    this.Merge(new StringLessThanOrEqualGenerator());

In the code building configuration and session factory, add:


The full code for all string comparison operations is available for download here.



5 thoughts on “NHibernate String Comparison

  1. Hi,

    I implemented the four NHibernate string comparison extensions exactly as you have shown. However, at runtime I get an

    “Unable to cast object of type ‘Antlr.Runtime.Tree.CommonTree’ to type ‘NHibernate.Hql.Ast.ANTLR.Tree.IASTNode’.”

    exception thrown from the BuildHql method. My linq is as follows:

    IQueryable query = dao.GetAll();
    query = query.Where(a => a.RegNum.GreaterThan(filterCriteria.RegNum));

    Any insights are greatly appreciated. Thanks!

  2. instead of ‘targetObject’ for the first parameter in the generator, use ‘argument[0]’. In the same generators, use ‘argument[1]’ where he had argument[0].

    Essentially, you should always use argument[0] and argument[1] within his generator code.

  3. Pingback: Calling a User Defined TSQL Function using NHibernate Linq | devioblog

  4. Pingback: Retrieving Length of Varbinary Field in NHibernate Linq | devioblog

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.