NHibernate SELECT MAX()

February 3, 2016

A form used to edit or add records was to set a number field to the highest assigned integer value plus one.

This is typically achieved by writing

SELECT Max(NumberField)+1 FROM [Table]

and in NHibernate you write something like

result.Number = session.Query<Table>()
    .Max(t => t.Number) + 1;

where Number is defined as int.

While this solution is principally correct, it fails if the table has no records:

Server Error in '/' Application.
Value cannot be null.
Parameter name: item
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentNullException: Value cannot be null.
Parameter name: item
Stack Trace:
[ArgumentNullException: Value cannot be null.
Parameter name: item]
 System.ThrowHelper.IfNullAndNullsAreIllegalThenThrow(Object value, ExceptionArgument argName) +4195637
 System.Collections.Generic.List`1.System.Collections.IList.Add(Object item) +32
 NHibernate.Util.<>c__DisplayClass4.<AddAll>b__2() +13
 NHibernate.Util.ArrayHelper.AddAll(IList to, IList from) +445
 NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) +573
 NHibernate.Impl.StatelessSessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) +329
[GenericADOException: Could not execute query[SQL: SQL not available]]
 NHibernate.Impl.StatelessSessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) +379
 NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) +145
 NHibernate.Impl.AbstractQueryImpl2.List() +117
 NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) +36
 NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +50
 NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +11
 System.Linq.Queryable.Max(IQueryable`1 source, Expression`1 selector) +283

The problem is, of course, that SELECT MAX(NumberField) on an empty table results in a single record containing a NULL value for the computed column, and the NHibernate mapper throws an exception trying to assign the NULL value to the int property.

Additionally, the SELECT statement above should also be written as

SELECT ISNULL(MAX(Number), 0) + 1

to generate 1 for the first record, which already points in the right direction.

The correct NHibernate statement is therefore

result.Number = session.Query<Table>()
    .Max(t => (int?) t.Number) ?? 0 + 1;

casting the C# int property to a Nullable<int>, which accepts the NULL value resulting from MAX(). The ?? operator is the equivalent of T-SQL’s ISNULL() function.

Fundamentally, the behavior is caused be the definition of the Queryable.Max() method:

public static TResult Max<TSource, TResult>(
  this IQueryable<TSource> source, 
  Expression<Func<TSource, TResult>> selector);

If the selector returns int, then the method also returns int. Could the result type not have been declare as Nullable<TResult>?

What does Linq to Objects do? This simple code

IEnumerable<int> values = new int[0];
Console.WriteLine(values.Max());

throws an InvalidOperationException with the message

Sequence contains no elements

as the corresponding Max() method also does not handle Null values. The correct way in Linq to Objects is to call DefaultIfEmpty() to replace an empty enumerable with one containing a single-element default value:

IEnumerable<int> values = new int[0];
values = values.DefaultIfEmpty();
Console.WriteLine(values.Max());

So, I experimented a bit to find a meaningful solution

public static T? max<T>(IEnumerable<T> values) 
  where T: struct, IComparable<T>
{
  T? result = null;
  foreach (var v in values)
    if (!result.HasValue || (v.CompareTo(result.Value) > 0))
      result = v;
  return result;
}
public static T max<T>(IEnumerable<T> values) 
  where T : class, IComparable<T>
{
  T result = null;
  foreach (var v in values)
   if (result==null || (v.CompareTo(result) > 0))
     result = v;
  return result;
}

only to find that

  • C# does not include generic constraints into method signatures, causing the code to not compile
  • Enumerable.Max() and Queryable.Max() are separate extension methods

In fact, Enumerable.Max() (which handles arrays, Lists, and everything IEnumerable) defines various overloads for base types, and one generic overload, whereas Queryable.Max() only defines the generic method. So the code above would have to be changed to base type-specific methods and one generic method.

Of course, the above code for IEnumerable would have no effect on how NHibernate handles the IQueryable.Max() method. This would have to be dealt with using NHibernate extensions.

Advertisements

Retrieving Length of Varbinary Field in NHibernate Linq

January 20, 2016

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", 
      visitor.Visit(arguments[0]).AsExpression());
  }
}

wired the new method into my registry class

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

which is itself registered in NH’s configuration

configuration.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();

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

internal class MsSql2008DialectFixed : MsSql2008Dialect
{
  protected override void RegisterFunctions()
  {
    base.RegisterFunctions();
    RegisterFunction("bytes_length", 
      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.


Calling a User Defined TSQL Function using NHibernate Linq

April 26, 2014

NHibernate Linq supports calling UDFs, but the implementation is not straight-forward.

I 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 class) and maps them onto Linq tree operations which in turn are translated into SQL by the NHibernate database driver.

This time, we want to access a user defined TSQL function which cannot be interpreted as a “method” of a “data type” because it contains generic parameters, such as integers or date values, and is not specific to a single table, such as this example on SO.

Nevertheless my solution is based on that example.

The function I want to access is called dbo.Display_Record and is used to decide whether a record can be displayed in the UI based on a given date range.

CREATE FUNCTION [dbo].[Display_Record]
  (@ValidFrom DATETIME, @ValidUntil DATETIME)
RETURNS INT AS
BEGIN
  RETURN 0 (false) or 1 (true)
END

Note that the return type of the function should not be boolean, due to the interoperation of NH and SQL Server (see bug report here) – use integer values instead.

First, we need a declaration and dummy implementation in C#

    public static class dbo
    {
        public static int DisplayRecord(DateTime? from, DateTime? until)
        {
            return 0;
        }
    }

Next, an Hql (the SQL-like language NH uses internally) generator registry must be registered for the method call “dbo.DisplayRecord”:

    public class DisplayRecordLinqToHqlGeneratorsRegistry
        : DefaultLinqToHqlGeneratorsRegistry
    {
        public DisplayRecordLinqToHqlGeneratorsRegistry()
            : base()
        {
            RegisterGenerator(ReflectionHelper.GetMethodDefinition(
                () => dbo.DisplayRecord(null, null)), 
                new DisplayRecordMethodsHqlGenerator());
        }
    }

And an Hql generator

    public class DisplayRecordMethodsHqlGenerator
        : BaseHqlGeneratorForMethod
    {
        public DisplayRecordMethodsHqlGenerator()
        {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition(
                  () => dbo.DisplayRecord(null, null))
            };
        }

        public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
            ReadOnlyCollection<Expression> arguments, 
            HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            return treeBuilder.MethodCall("dbo.Display_Record",
                visitor.Visit(arguments[0]).AsExpression(),
                visitor.Visit(arguments[1]).AsExpression());
        }
    }

arguments[0] and arguments[1] are the arguments passed to our .Net function, and need to be added to the Linq tree.

Finally, our generator registry needs to be registered using Mapping By Code configuration:

            configuration.LinqToHqlGeneratorsRegistry
                <DisplayRecordLinqToHqlGeneratorsRegistry>();

Now we can write NH Linq queries such as

var records = session.Query<MyTable>
    .Where(record => dbo.DisplayRecord(record.ValidFrom, record.ValidUntil);

and the WHERE condition correctly references our UDF function.


NHibernate String Comparison

August 20, 2012

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(
      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.

 

 


3 Ways to Write a Query in NHibernate

April 15, 2010

Getting started with NHibernate and NHibernate Linq, I compared the various ways to write a database query in that framework.

I came up with the following alternatives:

using NHibernate criteria:

var foos = 
  session.CreateCriteria<Foo>()
    .AddOrder(NHibernate.Criterion.Order.Asc("Id"))
    .List<Foo>();

using NHibernate Linq:

var foos = 
  session.Linq<Foo>().OrderBy(f => f.Id);

using NHibernate Linq with Linq syntax:

var foos = 
  from foo in session.Linq<Foo>() 
  orderby foo.Id 
  select foo;

All 3 statements actually generate the same SELECT statement. I guess I prefer the 2nd version.