In an attempt to provide full disclosure I am a complete NOOBIE when it comes to NHibernate.  In fact I have only been using it for a few weeks or so. What this means is take everything I say in this post with a grain of salt cause there may be a better, easier way.

When exploring more of the really cool details on how to utilize NHibernate I ran into what I would call a ‘gotcha’ when using nested CreateCriteria commands.

In order to better explain, it would be a good idea to give you full background.  I was trying to replace a Linq-2-Sql query in which I was doing a lookup on a field in a child table.  The table layout below will better illustrate.

I was trying to create a query which would attempt to filter on the file name on the download item AND the episode number (these are the values given to me via iTunes).  My first attempt at it looked something like this

var result = dataSession.CreateCriteria( typeof( EpisodeITunesItem ) )
    .SetFetchMode( "Episode", FetchMode.Eager )
    .SetFetchMode( "ServerLocation", FetchMode.Eager )
    .Add( Expression.Eq( "FileName", fileName ) )
    .CreateCriteria( "Episode" ) // this creates the 2nd ICriteria
    .Add( Expression.Eq( "EpisodeNumber", episodeNumber ) )
    .UniqueResult();

What really shocked me when I looked at the output via NHProfiler, I did not expect to see that 2 queries had run.  In an attempt to figure out what was wrong I headed over to the NHibernate docs site, in particular the page about QueryCritiera.  After doing a bit of digging I realized my issue.  When you use .CreateCriteria multiple times it creates multiple instances of ICriteria (which as far as I can tell means multiple SQL statements).  As I knew I did not want to have multiple statements ran, I set out to tweak my statement to resolve the issue. 

Below is the code I came up with which only creates one sql statement.

        
var result = dataSession.CreateCriteria( typeof( EpisodeITunesItem ) )
	.CreateAlias( "Episode", "e" )
        .SetFetchMode( "Episode", FetchMode.Eager )
        .SetFetchMode( "ServerLocation", FetchMode.Eager )                
        .Add( Expression.Eq( "FileName", fileName ) )
        .Add( Expression.Eq( "e.EpisodeNumber", episodeNumber ) )
        .UniqueResult();

As you can see the differences between the 2 statements is subtle, but huge.  The thing to look for is how I replaced .CreateCriteira with .CreateAlias and use the new alias in my where (.add) part of the statement.

Till next time,