Derik Whittaker

Syndication

News


Simple example of using a Subquery in NHibernate when using ICriteria

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.

 

image

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,


Posted 04-06-2009 4:41 PM by Derik Whittaker
Filed under: ,

[Advertisement]

Comments

DotNetShoutout wrote Simple example of using a Subquery in NHibernate when using ICriteria - Derik Whittaker - Devlicio.us
on 04-06-2009 6:59 PM

Thank you for submitting this cool story - Trackback from DotNetShoutout

DotNetKicks.com wrote Simple example of using a Subquery in NHibernate when using ICriteria
on 04-06-2009 7:04 PM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Jack wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-07-2009 11:00 PM

Something like the LINQ to Entity(SQL), I like the expression, but I think it will hurt the performance.

Jack wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-07-2009 11:01 PM

Something like the LINQ to Entity(SQL), I like the expression, but I think it will hurt the performance.

Tuna Toksoz wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-08-2009 11:51 AM

Why does it hurt in performance? The IN clause?

mwardm wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-09-2009 5:13 AM

Obviously database optimisers can always make such things moot and I'm also not aware of your cardinality, but from your problem description it looks very much like your SQL would have been better written by using a correlated EXISTS rather than the IN.

balance wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-10-2009 10:19 AM

How to do it with simple join (using criteria in NHibernate)? I was told that subquery is slower than join.

Douglas wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 04-10-2009 10:26 AM

How to do  it with simple join (using Criteria in NHibernate). I was told that subquery is slower than join.

David Morello wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 09-11-2009 3:40 PM

I have created the necessary files to recreate your example above but I keep getting the following error in NHProfiler - "The value "9" is not of type "MessageQueue" and cannot be used in this generic collection."

The 9 is a value for MESSAGE_QUEUE_ID in the MESSAGE_QUEUE_STATUS table.  I can post all of my code if necessary, just let me know.

David Morello wrote re: Simple example of using a Subquery in NHibernate when using ICriteria
on 09-11-2009 4:46 PM

I realized what I was doing wrong.  I needed to change the Subqueries.PropertyIn to Subqueries.Eq.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)