NHibernate 2.0.1 Criteria: IsNull restriction throwing exception

Question

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 entityList = criteria.List();

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?

Thanks for any help...

Answer 1

Hard to say from your description, but some suggestions that might point you in the right direction:

1. Definitely try turning on DEBUG logging for NHibernate. It's almost always able to narrow down the issue
2. Find some items you expect to be returned and ask for them by ID. If that errors, you'll know it's an issue with the mapping instead of your query
3. Does session.CreateQuery("from Order where ShippedOn is null").List() work?

Answer 2

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

Answer 3

I've seen this kind of exception before when there's a mismatch between your domain model, mapping, and database. For example if you have a nullable DateTime field in your database, but not a nullable property on your model.

I described it in a post on my blog a while back. I can't say for sure this is your issue, but it certainly sounds familiar.

Answer 4

DateTimes are not nullable in .net. Have you tried changing your domain model to use DateTime? which is nullable?

You can get similar exceptions using criteria queries. Suppose I have a Person domain object with a string property called Name. I can construct the following criteria:

ICriteria criteria = session.CreateCriteria(typeof(Person)
.Add(Restrictions.Eq(1234))

however is I list this criteria NHibernate will throw a type mismatch exception because I'm testing a string against an int. Behind the scenes NHIbernate does some clever reflective type checking on the mapped objects and will throw exceptions if the types don't line up. (you'd probably get a SqlException if it wasn't this clever)