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.


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.


Dealing with NHibernate System.IndexOutOfRangeException

September 4, 2013

Whenever I call SQL Server stored procedures from an NHibernate (3.2) application, I generate proxy methods that look like this

IList<T> MyProcedure<T>(ISession session
  , [some parameters]
)
{
  IQuery sp = session.CreateSQLQuery(@"BEGIN
    EXECUTE MyProcedure
            :SomeParameter
END")
    .AddEntity(typeof(T));
  sp.SetParameter<[datatype]>("SomeParameter", SomeParameter);
  ...;
  sp.SetResultTransformer(Transformers.RootEntity);
  //sp.SetTimeout(timeout);
  return sp.List<T>();
}

and manually create the data class MyProcedureResult and the corresponding mapping class MyProcedure_Result.

The SP is invoked like this:

var result = Procedures.MyProcedure<MyProcedureResult>(session, [parameters]);

This usually works, but recently I experienced an exception when calling a specific SP:

[Data]

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.IndexOutOfRangeException: [Data]

Source Error: 

Stack Trace: 

[IndexOutOfRangeException: [Data]]
   System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +5043194
   System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +67
   NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:363
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:236
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:195
   NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:131
   NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:2511
   NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:988
   NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:943
   NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:875
   NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:334
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:468
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:246
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1590

[GenericADOException: could not execute query
[ BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END ]
[SQL: BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END]]
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1599
   NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1497
   NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1487
   NHibernate.Impl.StatelessSessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:376
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:353
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:363
   NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:160

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547

As you can see, NH expects the column name [Data], which does not exist in the result set, causing GetOrdinal() to throw an exception.

What’s going on here?

Well, the stored procedure returns a result set containing the column name Data, but NH tries to find the column [Data].

After stepping through the source code of NH 3.2, I claim to partially understand how this happens.

When the entities and mappings are initialized, NH calls connection.GetSchema(DbMetaDataCollectionNames.ReservedWords) to check whether column names are reserved words. If a column name is found to be a reserved word, the name is flagged to be quoted and/or aliased when used in SQL.

If, however, the column name occurs in the result set of an SP, aliasing cannot be applied, and quoting is not required.

Quoting using backticks (`name`) as proposed on SO did not help either.

After many experiments, my solution was to simply rename the columns in the SP (DataX), and add a column name mapping

map.Column("DataX");

to the class map.

 


Solving NH Performance with Stateless Session (and Handling Lazy-Loaded Values)

July 16, 2013

Developing a web application (ASP.Net MVC, NHibernate, SQL Server) I needed to implement a couple of HTML reports.

I designed a database view, mapped it in NH, and retrieved the data using Query<>. Worked nice on dev, but on prod the page to 15 seconds to fetch and render 1000 records.

My first thought was that the view (1 CROSS JOIN, some LEFT JOINS) was the culprit, but running it from SSMS returned the complete result set immediately.

Searching for “NHibernate”, “performance” and the usual keywords I finally found the solution: StatelessSession.

The NH SessionFactory provides two kinds of sessions:

With OpenSession(), all objects created from database retrieval are managed in NHibernate’s caching system, whereas OpenStatelessSession() does not cache the objects. Since we do not need to manipulate the data objects when generating reports, the stateless session should be first choice for reports.

After implementing the changes, I immediately hit the next problem:

If you retrieve an object that is detached from the NH session, because it had been retrieved using a stateless session, you cannot access the lazy-loaded properties.

Well, in fact you can, but you need to Select() all required columns, including the lazy-loaded columns, in the lambda

statelessSession.Query<MyTable>
    .Select(t => new { t.column1, t.lazyloadedcolumn1, ... })

Finally, with the stateless session and the fixed query in place, a request is completed in under 1 second for 1000 records! 😉

Sources:


NHibernate OneToMany surprises

March 19, 2013

Up to now, I did not care about NHibernate’s Sets and Bags, as my queries would target the details tables directly, and filter to the parent’s PK.

Now I tried to extend my Classes and ClassMaps generation code for Mapping.By.Code to Bags, and started out with something like this:

public partial class Foo
{
  public virtual int Id { get; protected set; }
  public virtual ICollection<Bar> Bars { get; set; }
}
public partial class Bar
{
  public virtual long Id { get; protected set; }
  public virtual Foo Foo { get; set; }
}

The mapping for Bar is not affected by the new Bars property:

public partial class Bar_Map: ClassMapping<Bar>
{
  public Bar_Map()
  {
    Table("Bar");
    Id(x => x.Id, map => ...);
    ManyToOne(x => x.Foo, map => ...);
  }
}

The master table gets a new collection property, depending on which NH mapping you use (SO):

Bag(x => x.Bars, bag =>
  {
    bag.Table("Bar");
    bag.Inverse(true);
    bag.Lazy(CollectionLazy.Lazy);
    bag.Cascade(Cascade.DeleteOrphans);
    bag.Key(k => {
      k.Column(col => col.Name("FooId"));  
          // this is the SQL column name
    });
  }, map => map.OneToMany());

So I try this code, retrieve a Foo, and count its Bars:

var foo = session.Get<Foo>(fooId);
Console.WriteLine(foo.Id);
Console.WriteLine(foo.Bars.Count());

Surprisingly, NH selects ALL Bar records into the collection, and counts the elements in the collection. That’s not what I expected.

On the NHibernate Pitfalls blog I found the hint to change the Lazy() setting to Lazy(CollectionLazy.Extra). And indeed, only a SELECT COUNT(*) was executed.

Somehow I was expecting the collection properties to be an alias for SELECT WHERE statements, so I tried things like foo.Bars.FirstOrDefault(), foo.Bars[0] (for IList) or foo.Bars.Take(1), but each of them always first populated the Bars collection in .Net, and only then retrieved the requested object from the collection, rather than issuing a separate SELECT.

For a complete list of documented surprises, see the NHibernate Pitfalls Index.


Invoking Stored Procedures generating C# Code using T4 Templates

February 5, 2013

When developing database applications, I usually start out with the database schema and have the necessary C# code (data classes, procedure calls, application-specific constants) generated by a couple of stored procedures. For my own projects, I create a batch file to build the various parts of the solution, all the way from code generation to publishing the application.

Maybe not everybody shares my preference for building-by-batch, so I was looking for an alternative way to invoke code generation in stored procedures, and I remembered working on a project a couple of years ago that used T4 Text Templates to generate C# code.

The first component of the T4 solution is MultipleOutputHelper.ttinclude which implements features such saving only changed files, handling check-out if connected to TFS, and much more.

Since I want to access the connection strings stored in web.config or app.config, I searched for sample code and found ConfigurationAccessor.tt.

The final T4 Template looks like this. The header includes both libraries and declares .Net namespaces:

<#@ template debug="True" hostspecific="True" language="C#" 
#><#@ output extension=".cs"
#><#@ include file="MultipleOutputHelper.ttinclude"
#><#@ include file="ConfigurationAccessor.ttinclude"
#><#@ assembly name="System.Data"
#><#@ import namespace="System.Data"
#><#@ import namespace="System.Data.SqlClient"
#><#

Next, we instantiate the objects declared in both libraries

  var manager = Manager.Create(Host, GenerationEnvironment); 
  manager.StartNewFile("Classes.generated.cs"); 

  var config = new ConfigurationAccessor((IServiceProvider)this.Host);
  var connStrEntry = config.ConnectionStrings["default"];

Depending on your build process, you may want to cause database errors (connection string, connectivity, execution) to raise either warnings or compiler errors:

  var warnings = true;
  var errors = new List<string>();

  if (connStrEntry == null)
  {
    errors.Add("Connection string named 'default' not found");
  }
  else
  {
    var connStr = connStrEntry.ConnectionString;

After checking the connection string, let’s connect to the database and execute the stored procedure.

The output of PRINT commands is retrieved using the InfoMessage event:

    try
    { 
      using (var conn = new SqlConnection(connStr))
      {
        conn.Open();
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
        {                                    
            this.WriteLine(e.Message);
        };

        SqlCommand cmd = new SqlCommand("dev_Generate_DAL_Classes", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
      }
    }
    catch(Exception ex)
    {
      errors.Add(ex.Message);
    }
  }

  manager.EndBlock();

If no errors occurred, save the generated file. Otherwise, either raise warnings or errors.

  if (errors.Count == 0)
  {
    manager.Process(true); 
  }
  else 
  {
    foreach(var error in errors)
    {
      if (warnings)
        this.Warning(Host.TemplateFile + ": " + error);
      else
        this.Error(Host.TemplateFile + ": " + error);
    }
  }
#>

I originally tried to store warnings and errors using the Host.LogErrors method. However, no matter which Error Code I set in CompilerError, the result would always be errors rather than warnings. Using the TextTransformation‘s Warning() and Error() methods did the trick.