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