How does NHibernate Projections.Max work with an empty table?

Question

I'm trying to get the maximum value of an integer field in a table. Specifically, I'm trying to automatically increment the "InvoiceNumber" field when adding a new invoice. I don't want this to be an autoincrement field in the database, however, since it's controlled by the user -- I'm just trying to take care of the default case. Right now, I'm using

session.CreateCriteria<Invoice>()
        .SetProjection(Projections.Max("InvoiceNumber"))
        .FutureValue<int>();

to get the biggest invoice number already in the database. This works great, except when there are no invoices already in the database. Then I get a System.ArgumentException: The value "" is not of type "System.Int32" and cannot be used in this generic collection. Changing to FutureValue<int?>() didn't solve the problem. Is there a way to tell NHibernate to map the empty string to null? Or is there a better way to accomplish my goal altogether?

The stack trace of the exception (at least the relevant part) is

NHibernate.HibernateException: Error executing multi criteria : [SELECT max(this_.[InvoiceNumber]) as y0_ FROM dbo.[tblInvoice] this_;
SELECT this_.ID as ID647_0_, this_.[NHVersion] as column2_647_0_, this_.[Description] as column3_647_0_, this_.[DiscountPercent] as column4_647_0_, this_.[DiscountDateDays] as column5_647_0_, this_.[PaymentDueDateDays] as column6_647_0_, this_.[Notes] as column7_647_0_, this_.[DiscountDateMonths] as column8_647_0_, this_.[PaymentDueDateMonths] as column9_647_0_, this_.[DiscountDatePeriod] as column10_647_0_, this_.[DiscountDateMonthlyDay] as column11_647_0_, this_.[DiscountDateMonthlyDayDay] as column12_647_0_, this_.[DiscountDateMonthlyDayMonth] as column13_647_0_, this_.[DiscountDateMonthlyThe] as column14_647_0_, this_.[DiscountDateMonthlyTheDOW] as column15_647_0_, this_.[DiscountDateMonthlyTheMonth] as column16_647_0_, this_.[DiscountDateMonthlyTheWeek] as column17_647_0_, this_.[PaymentDueDatePeriod] as column18_647_0_, this_.[PaymentDueDateMonthlyDay] as column19_647_0_, this_.[PaymentDueDateMonthlyDayDay] as column20_647_0_, this_.[PaymentDueDateMonthlyDayMonth] as column21_647_0_, this_.[PaymentDueDateMonthlyThe] as column22_647_0_, this_.[PaymentDueDateMonthlyTheDOW] as column23_647_0_, this_.[PaymentDueDateMonthlyTheMonth] as column24_647_0_, this_.[PaymentDueDateMonthlyTheWeek] as column25_647_0_ FROM dbo.[tblTermsCode] this_;
] ---> System.ArgumentException: The value "" is not of type "System.Int32" and cannot be used in this generic collection.
Parameter name: value
   at System.ThrowHelper.ThrowWrongValueTypeArgumentException(Object value, Type targetType)
   at System.Collections.Generic.List`1.VerifyValueType(Object value)
   at System.Collections.Generic.List`1.System.Collections.IList.Add(Object item)
   at NHibernate.Impl.MultiCriteriaImpl.GetResultsFromDatabase(IList results)

Answer 1

use....UniqueValue<int?>();

NH uses a non-generic IList in their MultiCriteria implementation. Which is used for FutureValue batching. see here for why List<int?> fails to add null through it's IList implementation. I'm surprised I've never run into this before. Avoid using nullable value types with Future or MultiCriteria.

Answer 2

With the QueryOver API:

Session.QueryOver<T>()
      .Select(Projections.Max<Statistic>(s => s.PeriodStart))
      .SingleOrDefault<object>();

if nothing is returned its null, otherwise cast the result as numeric