NHibernate Querying - Getting list of null objects

This weekend we had a very bizzare error on NHibernate. We did a query, nothing clever in it, but we got a list of null's back. The length of the list was the expected value but each item was null.

IList result = PeriodFactory.GetAllPeriods(person);
if (result[0] == null)
Throw New Exception("Item Was Null!");

It was throwing the null exception. So what was NHibernate doing?

Well, let's look at the mapping file:

It all looks OK. It certainly compiled and ran OK. So maybe it was something to do with the code querying the Periods:

p>Well, doesn't seem to be anything wrong here. Maybe NHibernate is not generating the SQL correctly. So using the NHibernate Profiler I checked out the SQL being generated:

Well, even this looks fine. Things are getting very strange! What could be going wrong. NHibernate has worked fine for the last 30 odd classes we'd persisted. What was so strange now? Well, another feature of the NHProfiler is to view the results that are returned. So that was the next stage:

This was the first clue we had. Notice the first column, it is blank, yet it is suppose to be the primary key. So what happens if we try loading a record based on the primary key. This is the most basic of operation, therefore surely this will work. A little hacking of the code for testing:

Three guesses for what result was? you guessed it, sweet Nothing, value 3 did exist in the database, so this should have worked. NHibernate was clearly not being able to create the object. So the query brought back the right number of results. The Get did not. Let's take a closer look at the table structure and compare the primary keys:

Wait! what is this? we have a column called PeriodId (highlighted) that is not the primary key. What is going on here, where did that column come from?

I rechecked the mapping file to located any stray PeriodId's but the only ones were the Foreign Key values which correctly are in the Referenced Table. Then I remembered SchemaUpdate. It was updating the table based on the schema's. My naming convention was class has PK of "Id" and the database was "{tablename}Id" I'd added the column attribute at a later stage and the Update had obviously updated the table to include the new column.

This must be considered a bug in the schema update as it should have updated the primary key and not created a random new column.

With thanks to my trusted memory (eventually) and the great NHProfiler we've managed to get to the bottom of this frustrating issue. Moral of the story, be careful with what the schema update is actually doing...