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

Type-safe Queries in Dynamics CRM 4

September 20, 2011

Avoiding string literals and writing type-safe code as supported by the compiler are among my favorite code-writing philosophies.

So when I started to work on a Dynamics CRM project, which by default uses strings for table and column names (fortunately, table names can be accessed by the EntityNames enumeration), I wished there was a Linq-like way to access the CRM Service.

Therefore, the first code I wrote was a method implementing a type-safe query encapsulating RetrieveMultiple():

public static List<T> RetrieveMultiple<T>(this CrmService service) 
  where T : BusinessEntity
{
  List<T> result = new List<T>();

  QueryExpression query = new QueryExpression();
  query.EntityName = typeof(T).Name;      
  query.ColumnSet = new AllColumns();

  BusinessEntityCollection retrieved = service.RetrieveMultiple(query);
  foreach (T item in retrieved.BusinessEntities)
    result.Add(item);

  return result;
}

While not very powerful and flexible, this method provides both type-safe compilablity and avoidance of string literals.

After some research, I found that there are already libraries supporting Linq queries for Dynamics CRM:

These libraries come with an overhead though:

  • LINQtoCRM requires all queries to be created through its CrmQueryProvider class.
  • xRM provides Linq queries through its XrmDataContext class which requires a connection string in the .config file and generating DTOs using CrmSvcUtil.

Both options may be challenging to include in an already existing project.


Converting Linq Expressions to T-SQL

August 5, 2011

In my post about a Batch Update command for Linq To SQL, the code I presented uses a method ToMSSqlString() which has not yet been defined.

The method converts a Linq Expression to a T-SQL expression by evaluating the current node of the expression tree and evaluation the node’s children:

public static class ExpressionExtensions
{
  public static string ToMSSqlString(this Expression expression)
  {
    switch (expression.NodeType)
    {
      case ExpressionType.Add:
        var add = expression as BinaryExpression;
        return add.Left.ToMSSqlString() + " + " + add.Right.ToMSSqlString();
      case ExpressionType.Constant:
        var constant = expression as ConstantExpression;
        if (constant.Type == typeof(string))
          return "N'" + constant.Value.ToString().Replace("'", "''") + "'";
        return constant.Value.ToString();
      case ExpressionType.Equal:
        var equal = expression as BinaryExpression;
        return equal.Left.ToMSSqlString() + " = " +
               equal.Right.ToMSSqlString();
      case ExpressionType.Lambda:
        var l = expression as LambdaExpression;
        return l.Body.ToMSSqlString();
      case ExpressionType.MemberAccess:
        var memberaccess = expression as MemberExpression;
        // todo: if column aliases are used, look up ColumnAttribute.Name
        return "[" + memberaccess.Member.Name + "]";
    }

    throw new NotImplementedException(
      expression.GetType().ToString() + " " + 
      expression.NodeType.ToString());
  }
}

This only implements selected NodeTypes to sketch the mechanism.

As an alternative, you can also use the ExpressionVisitor class to iterate the expression tree, as illustrated in other blogs.

Whichever method you use, the result of such a method is a T-SQL string representation of the Linq expression which can be used to build the final T-SQL command.


Get Name of Nested Property as String Value

January 21, 2011

I presented a method to retrieve the property name referenced by a lambda expression in a previous post.

Reader Jacek asked,

is there any way for nested property (eg. Studies.ID or Studies.Class.Name) to convert to string?

Nice question, and I tried to solve it.

Starting with a simple data model

public class Foo
{
  public int FooID { get; set; }
  public string Name { get; set; }
}
public class Bar
{
  public string Name { get; set; }
  public Foo BarFoo { get; set; }
}
public class Baz
{
  public string Name { get; set; }
  public Bar BazBar { get; set; }
}

we want to convert an expression such as b => b.BazBar.BarFoo.Name into its string representation.

public static string GetPropertyName(this LambdaExpression expression)
{

First, we handle type casts, as in the original solution:

  if (expression.Body is UnaryExpression)
  {
    UnaryExpression unex = (UnaryExpression)expression.Body;
    if (unex.NodeType == ExpressionType.Convert)
    {
      Expression ex = unex.Operand;
      MemberExpression mex = (MemberExpression)ex;
      return mex.Member.Name;
    }
  }

Keep a reference of the parameter, and set path (i.e. concatenated property names) to empty

  MemberExpression memberExpression = (MemberExpression)expression.Body;
  MemberExpression memberExpressionOrg = memberExpression;

  string Path = "";

The sequence b.BazBar.BarFoo is stored as an expression of ExpressionType MemberAccess, which is an object of the non-public class System.Reflection.RuntimePropertyInfo (google), as the debugger tells us.

Since the class is not public, we need to access the Member property using reflection, in this case PropertyInfo.GetValue:

  while (memberExpression.Expression.NodeType == ExpressionType.MemberAccess)
  {
    var propInfo = memberExpression.Expression
      .GetType().GetProperty("Member");
    var propValue = propInfo.GetValue(memberExpression.Expression, null) 
      as PropertyInfo;
    Path = propValue.Name + "." + Path;

    memberExpression = memberExpression.Expression as MemberExpression;
  }

  return Path + memberExpressionOrg.Member.Name;
}

The variable Path stores the sequence of property names as we iterate through the expression tree. Finally, the last property name is added.

Using this code, the line

Console.WriteLine(
  ExpressionExtensions.GetPropertyName(
    (Expression<Func<Baz, string>>)(b => b.BazBar.BarFoo.Name)));

produces this output:

BazBar.BarFoo.Name

Get Property Name as String Value

July 27, 2010

If you use an ORM (such as EF or NHibernate), you end up with classes for each mapped table, and its fields mapped to properties:

public class Foo
{
  public int FooID { get; set; }
  public string Name { get; set; }
}

To get the name of each property as a string, use the following generic method:

public class Tools
{
  public static string GetPropertyName<P, T>(Expression<Func<P, T>> expression)
  {
    MemberExpression memberExpression = (MemberExpression)expression.Body;
    return memberExpression.Member.Name;
  }
}

If you call this method explicitly, you need to pass the types of both the class and the property type in the invocation, e.g.:

Tools.GetPropertyName<Foo, string>(f => f.Name)

If you use a generic class declaring the data class, the compiler can infer the type of the property, and there is no need to provide both types:

public class Test<Class>
{
  public static string GetPropertyName<T>(Expression<Func<Class, T>> Field)
  {
    return Tools.GetPropertyName(Field);
  }
}

Test<Foo>.GetPropertyName( f => f.Name );

Sometimes (*) the compiler includes a Convert function into the expression tree to convert between the original and target data type of the property. We can extend the GetPropertyName() function to take care of the Convert function:

public static string GetPropertyName<P, T>(Expression<Func<P, T>> expression)
{
  if (expression.Body is UnaryExpression)
  {
    UnaryExpression unex = (UnaryExpression)expression.Body;
    if (unex.NodeType == ExpressionType.Convert)
    {
      Expression ex = unex.Operand;
      MemberExpression mex = (MemberExpression)ex;
      return mex.Member.Name;
    }
  }

  MemberExpression memberExpression = (MemberExpression)expression.Body;
  return memberExpression.Member.Name;
}

(*) I met this condition when dealing with overloaded generic methods, and the C# compiler could not infer the correct method from the property types given as parameters.


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.


Logging SQL Statements generated by NHibernate

April 14, 2010

Asked how to log the SQL statements generated by NHibernate, the internets answer: define a log4net appender, since NHibernate uses log4net.

You need to add nhibernate and log4net sections to your app.config (or web.config) file:

<configuration>
  <configSections>
    <section name="nhibernate" type="System.Configuration.NameValueSectionHandler,
      System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <section name="log4net"
      type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
  </configSections>

These <section> entries allow to add user-defined tags under the <configuration> tag.

For NHibernate, we need to activate logging:

<nhibernate>
  <add key="hibernate.show_sql" value="true" />
</nhibernate>

Then we define appenders for log4net:

<log4net>
  <appender name="DebugSQL" type="log4net.Appender.FileAppender">
    <param name="File" value="c:\projects\nhlog\nhsql.log"/>
    <param name="AppendToFile" value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern 
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
  </appender>
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG" />
    <appender-ref ref="DebugSQL" />
  </logger>
  <appender name="DebugNH" type="log4net.Appender.FileAppender">
    <param name="File" value="c:\projects\nhlog\nh.log"/>
    <param name="AppendToFile" value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern 
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
  </appender>
  <logger name="NHibernate" additivity="false">
    <level value="ALL" />
    <appender-ref ref="DebugNH" />
  </logger>
</log4net>

Somewhere in your startup code, initialize log4net to read from app.config:

log4net.Config.XmlConfigurator.Configure();

The NHibernate.SQL logger will only receive generated SQL statements. The NHibernate logger receives every log message. Output is always filtered by the level parameter.

Thanks to the internet goes here, here, here, here, and here.

I have previously written about tracing SQL Statements generated by Entity Framework, and where I saved every SQL statement to a separate file. To accomplish this, one would need to implement their own log4net appender, I guess.