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!

Integrating NHibernate with Spring.NET and MVC3

We have seen in one of the previous post that its not very difficult to integrate Spring.NET with MVC3 application.  Almost every real project has a need to talk to a database for doing stuff.  When it comes to interacting with the Database, NHibernate does an Awesome job. 

I have used NHibernate with legacy database as well as databases that were designed from scratch.  IMHO, other competing products have a long way to catchup before they can be compared with the flexibility that NHibernate offers!

Recently, in one of MVC3 project we integrated NHibernate using the Fluent API with Spring.NET.  This process is not very difficult but it involves a few steps.  I decided to document the steps for the benefit of the community.

We will continue from where we left in the previous post, if you did not read the previous post already then, I strongly recommend reading it to understand how you can integrate Spring.NET with MVC3 application.

Very well than, Lets start!

To integrate NHibernate using the Fluent API in a Spring.NET MVC3 application one has to do the following

• Make the Web.config changes to include the Parsers config section in the spring configuration.
• Register the OpenSessionInView HTTP Module in web.config.  We will be using single session per request, NHibernate session handling strategy.
• Add the reference to the required DLL's
• Register the DB provider in the spring configuration file.
• Register the NHibernate Session Factory in the spring configuration file
• Write a custom implementation of the NHibernate Session Factory that loads and registers the Assembly that has Fluent NHibernate mappings in it.
• Register the Transaction Manager in the spring configuration file
• Enable the Spring AOP Transactions using attributes in the spring configuration file
• Test out the NHibernate integration

Lets execute the steps one by one.  Please remember I will be continuing from where I left in the previous post.  Hence, I will be assuming that you already have Spring.NET correctly integrated in your MVC3 application.

Step - 1 - Make the Web.config changes to include the Parsers config section in the spring configuration

To integrate only Spring.NET with MVC3 application we had to add a configurationSection called context.  To integrate NHibernate with Spring.NET in a MVC3 application we have to add another configurationSection called parser.  This section will help Spring.NET understand the Database and Transaction configuration.  Lets look at how its done.

Step - 2 - Register the OpenSessionInViewModule HTTP Module in web.config. We will be using single session per request, NHibernate session handling strategy

Why do we need the OpenSessionInViewModule HTTP Module?

This will ensure that before the request is processed by any Controller, NHibernate session is opened and attached to the current thread.  And just before sending the response to the client, the NHibernate Session will be flushed and closed.  This enables us to do lazy loading even when the View is being rendering.  Lets look at the web.config change required to enable this:

Step - 3 - Add the reference to the required DLL's

For integrating NHibernate with Spring.NET and MVC3 application we need to add the references of following spring DLL's

• Spring.AOP.dll -> Used to enable AOP using Spring.NET.  This will be heavily used when we enable transactions in our application.
• Spring.Data.dll -> Used to enable the Spring.NET Database module
• Spring.Data.NHibernate30.dll -> Used to integrate Spring.NET with NHibernate
• NHibernate.dll -> The core NHibernate DLL
• Iesi.Collections.dll -> Provides the ISet interface and its implementation
• FluentNHibernate.dll -> Used to enable FluentNhibernate configuration for NHibernate
• log4net.dll -> Used by NHibernate for logging.  Lot of effort has been put into making the NHibernate log as detail as possible and as readable as possible.  This is a great tool when it comes to debugging something that you cannot understand easily
• NHibernate.ByteCode.Castle.dll -> Used by NHibernate to enable Dynamic Proxies
• Castle.Core.dll -> Used by NHibernate to enable Dynamic Proxies using Castle

Step - 4 - Register the DB provider in the spring configuration file

This step is pretty simple.  We need to inform NHibernate about the Database.  This is done by registering a DbProvider in the spring configuration file.  We will be registering the DbProvider in the web.xml (web.xml was the file we created in the previous post.  This hold all the Spring.NET configuration).  Add following configuration to the web.xml

Notice that we have to define the db name space in the objects root tag. Please update the above configuration with the DB Credentials that you are using.

Step - 5 - Register the NHibernate session Factory in the spring configuration file

Session Factory is a heavy weight NHibernate object.  Only one instance of SessionFactory is create per database.  Lets look at the Spring.NET configuration file for registering the NHibernate Session Factory.

The above configuration registers a NHibernate session factory which is of type aspnetwebFormsWithMvc3App.NHibernate.FluentNhibernateLocalSessionFactoryObject and is found in the assembly aspnetwebFormsWithMvc3App.  This is a custom class that we will create later in the post.  It has various properties like

• Which DbProvider to use
• What are the FluentNhibernateMappingAssemblies.  This is a list of assembly names which our NHibernate Session factory class will search for to locate Fluent Nhibernate mappings
• What are the MappingAssemblies.  This is a list of assembly names which has normal NHibernate mappings
• What are the HibernateProperties.  These are the key value pairs of NHibernate properties that we would want to use.  We are setting the connection provider, dialect, driver_class and sql_sql values

Step - 6 - Write a custom implementation of the NHibernate session factory that loads and registers the Assembly that has Fluent NHibernate mappings in it

We have to provide a custom implementation of NHibernate Session Factory class so that we could load our Fluent NHibernate assemblies.  These assemblies will have the Fluent NHibernate mapping classes.  The code of our Custom NHibernate Session Factory looks like this

Step - 7 - Register the Transaction manager in the spring configuration file

NHibernate always interacts with the database using Transactions.  We would definitely want to control how and when the transactions are started and ended.  For that to happen we need to register the transaction manager in the spring configuration file.  Add the following to the web.xml spring configuration file.

Step - 8 - Enable the Spring AOP transactions using attributes in the spring configuration file

We would want to govern how and when the transactions are started using Attributes.  For this to happen we need to inform Spring.NET that we are going to use Attributes to define the transactional behavior we need.  Spring.NET reads these attributes and starts/commits/rollbacks transactions using AOP.  To inform spring.net about our intentions add the following line in web.xml spring configuration file.

Notice that we added the line tx:attribute-driven but we also added the namespace tx in the objects root node declaration.

If you have followed all the steps so far then you have successfully Integrated NHibernate with Spring.NET in an MVC3 application.

Step - 9 - Test out the NHibernate integration

To test out our brand new NHibernate integration with Spring.NET in an MVC3 application, lets change the implementation of the AccountService.UserCount method.  Currently the AccountService.UserCount method returns a hard coded value of 5454.  We will now replace it with the correct implementation, we will get the record count from the User table.

We will have to create an NHibernate entity that maps to the User table.  Lets create the User class that will map to the User table.  We will also create a class called UserMap class that will hold the NHibernate Fluent Mappings for the User class.

The details are embedded in comments. We have the User entity class and the UserMap mapping class to hold Fluent NHibernate mappings.

Now, its time to actually use the User class to get the count of number of users in the database.  To do that, we will have to inject the SessionFactory instance in the AccountService.  Updated Spring.NET configuration file web.xml would look like

After injecting the SessionFactory its time now to change the implementation of the UserCount method to return the count of users from the users table. Updated AccountService looks like

Since we have registered the OpenSessionInViewModule HTTP Module the GetCurrentSession method of SessionFactory instance will return the same session instance that was created by the HTTP Module. This is absolutely vital for the Lazy loading to work correctly.

Also notice that we have added the [Transaction] attribute to the method UserCount with TransactionPropagation as Required.  This will make sure that whenever the method UserCout is invoked either a new transaction is started or the method will join any existing transaction started earlier in the same thread.

All set!  Its time to run the server and browse the /Home/SpringIndex URL to see if our integration with NHibernate works!

Showing the actual user count using NHibernate

As show in the image we are successfully getting the UserCount from the database.  We have successfully integrated NHibernate with Spring.NET in an MVC3 application!