Of the many things that NHibernate is, simple and ‘straight forward’ it is not (at times).  Recently I needed to do a SubQuery, so of course the first thing I did was try to Google it.  I was able to find quite a few examples on how to create and execute a subquery, but none of them had ALL the information I was looking for.

What was I looking for, well the ‘dummy’ overview on what I should do.  To be exact I wanted the following:

• Database layout (most had this, not all)
• Raw sql as if you were doing it by hand (a few had this)
• Clean/Simple example of how to create the SubQuery (some had this)
  • Details on what the query was actually doing (not sure I saw this)
• The generated output of the sql which was created by NHibernate (some had this)

Before we get started with creating and looking at the code, maybe it would be best if I gave an overview of the what I was trying to do which required a subquery.  I needed to return all header records (from Message_Queue table) who had a child record (from Message_Queue_Status table )which was in a given state. 

Database Diagram:
Below you will find a simple database diagram of the 2 tables I was trying to query against.  The parent table in this diagram is the Message_Queue table.  This table hold the main information for a message which is going to be sent in our system.  The child table is the Message_Queue_Status table, this holds a record for the message for each step in the process.  With this table we can know at any point in time what status the sending message is in.

Raw SQL of what I needed NHibernate to generate:
What I did prior to trying to setup NHibernate was to manually create the sql for what I needed.  I do this many times because I can whip out the sql pretty quickly and easily and it provides me a better picture of what I need NHibernate to do.  The raw sql I created is below.  (Where you see the values 2 & 4, those would be enums when translated to NHibernate)

SELECT MQ.*
FROM [MESSAGE_QUEUE] MQ
WHERE MQ.[DIRECTION_TYPE_ID] = 2
	AND MQ.[MESSAGE_QUEUE_ID] IN ( 
		SELECT MESSAGE_QUEUE_ID FROM [MESSAGE_QUEUE_STATUS] WHERE [STATUS_TYPE_ID] <= 4
	 )

NHibernate code needed to create the query:
Once I knew what I needed to do, it was only a matter of translating that need over to NHibernate.  The thing that stumped me at first was how exactly to create the subquery.  The code below is the final working example needed to build the equivalent of the sql above.

var subCriteria = DetachedCriteria.For();
subCriteria.SetProjection( Projections.Property( "MessageQueue.MessageQueueID" ) )
	.Add( Expression.Le( "StatusTypeID", 4 ) );

var criteria = session.CreateCriteria( typeof( MessageQueue ) )
	.Add( Expression.Eq( "DirectionTypeID", (Int32) MessageDirectionType.Outbound ) )
        .Add( Subqueries.PropertyIn( "MessageQueueID", subCriteria ) );

Taking a look at the NHibernate code above, you really need to break down into two parts.

1. The subquery
   The top part is the logic/code needed in order to build:
   SELECT MESSAGE_QUEUE_ID FROM [MESSAGE_QUEUE_STATUS] WHERE [STATUS_TYPE_ID] <= 4
   
   Once you have this part built you will need to apply it to the main part of the query
2. The main query
   The main query is where all the magic happens.  This is where you need to apply your sub-select and where the data will be filtered.
   
   You will notice that I am using the ‘Subqueries.PropertyIn’ syntax.  This will generate the the following SQL
   MQ.[MESSAGE_QUEUE_ID] IN ( ….. ) which is what you are looking for.
   You should also take note that I am using “MessageQueueID” as my property, but when it gets rendered it is the correct column name in the db as defined by the mappings.  I point this out as many first time NHibernate users (myself being one of them) will try to use the db column name, not the property name.

Now if you have done everything correctly you should be able to run your sub query and it will produce the data you are looking for.

I will also point out that if you are NOT using NHProfiler for your NHibernate development, you are simply wasting debug cycles.

Hope this helps someone.

Till next time,