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 Responses to NHibernate String Comparison

  1. Scott says:


    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. mike says:

    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. Heydar says:

    Thank you, that was very helpful. But to make it work the point commented by mike should be considered.

  4. […] previously wrote about adding support for .Net string comparison functions in NHibernate Linq. This strategy uses existing .Net methods (such as methods of the System.String […]

  5. […] used NH generators already once or twice, I wrote an extension […]

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 )

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

%d bloggers like this: