I've been utilizing NHibernate 2.0.1.4000
on all current .NET 3.5 SP1 projects and have had no problems with any other queries (utilizing either the Query or Criteria APIs), until some new business logic dictated the necessity of a new query in this particular project against the application's database that needs to retrieve records with a specific null property (of type DateTime) from a single table.
Knowing that you cannot use the not-equal restriction for this type of query, but instead can use the IsNull restriction, this simple query generates a "Value cannot be null!" Exception when executed. I have extensive DEBUG mode log4net log files, which I have reviewed and haven't yet helped, and I have verified that my class for the table does specify the property I'm checking for is a nullable property (DateTime?) to avoid the problems that can cause by forcing updates to the record, etc., which isn't happening here…
Here's the query, nothing complex, and I've tried with/without the MaxResults additional restriction to eliminate it as a problem, and yet, everytime, the exception gets thrown before I can collect the results:
ICriteria criteria = session.GetISession().CreateCriteria(typeof (Order)).Add(NHibernate.Criterion.Restrictions.IsNull("ShippedOn")).SetMaxResults(10);
IList<Order> entityList = criteria.List<Order>();
Any ideas or pointers to more information that might help me solve this? I've tried using HQL alternatively, same problems… Am I missing something here with regards to returning records with a specific null property?
Best Answer
Quick update...but it turns out after further investigation, that the Expection is actually being thrown when the transaction and unit of work is complete, and the session .Flush() method is called, and again somehow relates to how NHibernate tries to deal with a nullable table field/DAO property. Which even though I've handled in my class and mapping for the applicable table, the actual SQL my criteria with restrictions is generating, causes the exception to be thrown on Flush by a SqlDateTime issue...
For now, my workaround has been to retrieve records from my first restriction and handle the IsNull check in the code instead of the query. Not as performant, but until I figure it out, it's working...