Pop quiz, what is the difference between Expression.Lt (.Gt, .Eq, etc) and Expression.LtProperty (.GtProperty, .EqProperty, etc)? If you know the answer off the top of your head you win the prize… What is the prize you may ask? NOTHING….

Anyway, I needed to create a criteria query where I wanted to filter out the data where the date in one table was Less Than the date in another table.  I thought this was pretty straight forward.  I created an expression part like below

criteria.Add( Expression.Lt( “DateFieldOne”, “DateFieldTwo ) );

However, when I ran my test to see if my criteria was correct I received the following error:

NHibernate.QueryException: Type mismatch in NHibernate.Criterion.SimpleExpress: DateFieldOne expected type System.DateTime, actual System.String

I have to say that I as stumped, everything looked right and I even took a look at the restriction information which was generated.  You can see it below.

{(DateFieldONe < DateFieldTwo)}

To the untrained eye (that would be mine) it looked right.  After a few minutes of staring at this (and scratching my ass for a bit :) ) it dawned on me.  NHibernate was not comparing my fields, it was trying to compare the column on the left (DateFieldOne) to the ‘string’ value of DateFieldTwo.  At this point I knew what I needed to do… Use the .LtProperty.  When I changed from .Lt to .LtProperty everything worked out just right.

My new criteria logic looks like:

criteria.Add( Expression.LtProperty( “DateFieldOne”, “DateFieldTwo ) );

So the moral of the story is this.  When you are trying to create a criteria selection and you are trying to compare 2 columns, not a column against a value make sure you use the *Property command on the Expression object (.LtProperty, .GtProperty, .EqProperty, etc).

Hope this helps,

Till next time,