How to map Fluently mapped NHibernate Entities To Stored Procedures ResultSet

In one of our .NET/NHibernate project, we wanted to move a time and resource consuming NHibernate generated query to a Stored Procedure.  Nothing else should change, the stored procedure should return the same result set as the query.

The motivation behind this move was to use some native techniques to tune/optimize the query for example compute some intermediate result set and put into a temporary table etc.

Now that the requirements are clear lets dive into the problem a bit more.  We were using Fluent NHibernate to map our entities and the problematic query was generated using NHibernate's criteria API using one of the Fluently mapped NHibernate Entity.  This object was a pretty big object with numerous associations to other NHibernate Entities.

I (being a lazy developer) was in no mood to map the result set of Stored Procedure all over again in an hbm.xml file.  Basically I was trying to do as little as possible to get the job done :).  What I had in mind was to directly map the result set of Stored Procedure to already mapped Fluent NHibernate Entities.

DISCLAIMER: Before I say anything else, let me make it very clear that its was not NHibernate that was generating an un-optimized query, our schema is a legacy schema and the query itself was pretty complex to begin with.  In all likelihood its a problem caused by us not using NHibernate correctly.

OK enough of context setting, let jump into code.

How Do They Do It!

Lets assume that we have two NHibernate entities called Department and Employee.  Department has a one-to-many relationship with Employee (i.e. A Department can have many Employees).  The mapping files of Employee and Department are stock standard, but just for sake of completeness showing them here

Now lets say we have a method that queries the departments by Name.  We want to move this query to a Stored Procedure.  The method and the generated query (for reference)


To move this NHibernate generated query to a Stored Procedure we need to do the following steps

• Create a Stored Procedure that returns the exact same result set as the NHibernate generated query

• Configure the stored procedure as a Named Query in hbm.xml file.  

• Invoke the named query via NHibernate

That's about all the changes we need to map the result set of Stored Procedure to the Fluently mapped NHibernate entities.  As shown above all the lazy loading and other relationships work as before!

These simple steps got us what we wanted without some painful mapping jugglery.  As always, NHibernate just does not cease to amaze!

Thanks all folks!

Shameless Plug: we will be launching the best ever arithmetic app called "Math Monsters" very soon on the AppStore.  Checkout the details on our Website and like us on Facebook!