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:
- more on StackOverflow
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(
visitor.Visit(targetObject).AsExpression(),
visitor.Visit(arguments[0]).AsExpression());
}
}
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:
configuration.LinqToHqlGeneratorsRegistry <StringComparisonLinqtoHqlGeneratorsRegistry>();
The full code for all string comparison operations is available for download here.
devioblog 
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!
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.
Thank you, that was very helpful. But to make it work the point commented by mike should be considered.
Pingback: Calling a User Defined TSQL Function using NHibernate Linq | devioblog
Pingback: Retrieving Length of Varbinary Field in NHibernate Linq | devioblog