R – NHibernate 2.0.1 Criteria: IsNull restriction throwing exception

criteriaexceptionnhibernate

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

Related Solutions

C# – Nullable DateTime in NHibernate 2.0.1

This explains it:

if (!(entity is IAuditable))
    return false;

DateTime now = DateTime.Now;
state[Array.IndexOf(propertyNames, "CreateDate")] = now;
state[Array.IndexOf(propertyNames, "ModifiedDate")] = now;

return true;

But still wondering about the 2.* way to declare DateTime? in NHibernate mapping files.

[Update] Asked it in another question and got the answer: https://stackoverflow.com/questions/593236/nhibernate-2-mapping-files-how-to-define-nullable-datetime-type-datetime

R – NHibernate criteria query question

since you are doing this in each request maybe it should be better to set up two formula-properties in your entity. The first one should fetch the latest Gameplay-Id and the other the first Gameplay-Id with a not Null property

this could be as such in the xml mapping file of Entry

<property name="LatestGameplay" formula="select top(1)gp.Id from Gameplay gp where gp.FK_EntryId = PK_EntryId order by gp.InsertDate desc" />

this leaves you with the Gameplay Id's on the Entry entity and after you fetch it it would require another round trip to the DB to GetById-fetch the gameplay's

Alternatively you could work-around using filters. Set the collection back to "lazy" and create these nice filters

Gameplay latest = NHibernateSession.CreateFilter(entry.GamePlays , "order by InsertDate desc").SetMaxResults(1).SetFirstResult(1).UniqueResult<Gameplay>();

Gameplay winner = NHibernateSession.CreateFilter(entry.GamePlays , "where FK_PrizeId is not null order by InsertDate asc ").SetMaxResults(1).SetFirstResult(1).UniqueResult<Gameplay>();

And IFilters can be used in a multiquery as so have 2 db hits: one for the original Entry and one for the multiquery.

Last but not least, you could define 2 bags in the Entry entity, one IList<GamePlay> Latest and one IList<Gameplay> Winner which in the Entry mapping file would be filtered with the appropriate query (although i don't remember now if you can define TOP clauses in the filters) and set those as non-lazy. Then with a single round-trip you can have all the data you want with the following (ugly) syntax

Entry entry = findEntry(phoneNumber);
Gameplay winner = entry.Winner[0]; //check this if null first
Gameplay Latest = entry.Latest[0]; //ditto

note that of all the solutions the 3rd is the one that provides a mechanism to generate additional queries, as the bag can be used in a Criteria/HQL query

Best Answer

Related Solutions

C# – Nullable DateTime in NHibernate 2.0.1

R – NHibernate criteria query question

Related Topic