Using Projections and Transformers in NHibernate

.NET Musings

Wandering thoughts of a developer, architect, speaker, and trainer


Using Projections and Transformers in NHibernate

Last night at the kickoff for CINNUG’s BitSlingers spinoff, one of the questions that came up dealt with using NHibernate to query a data store for usage counts by user, role, and menu option.

Although I am going to present how to do it in NHibernate, I want to be clear that this goes against what I recommend.  NHibernate is best at CRUD operations working with Domain Objects.  The question was how to create reporting type data in NHibernate, and my rule of thumb goes something like this (this alone could fill a blog post, or maybe it’s my next article for NPlus1):

Use NHibernate for CRUD.  Using {fill in the blank} for reporting.

That being said, let’s tackle the problem at hand. Let’s say we have a table defined as the image below.  What we want to return is the usage stats grouped by MenuOption, UserName, and RoleName.  (My initial recommendation? Write a stored procedure to do this and use ADO to get the values.  But I digress.  That was not an option in this particular case.)


The original code (shown at the end of this post) consisted of three functions.  The main function first called a function to get the data (returning an ArrayList of object[]), then passed each element of the ArrayList into another function which read the object array (by hardcoded position) and created a new LoggingList object that was then added to an IList<LoggingList> (gasp).

This sample was brought to the group because, well, it smelled.  Without going into great detail, here are my main concerns with this approach:

  1. The developer used HQL instead of ICriteria.  There are time when HQL is necessary, especially when joining.  However, this was a query against a single Domain Object, and the ICriteria method is cleaner and will typically generate better SQL.  It is also inherently transparent across Dialects.
  2. Depending on “Magic” numbers.  The new Logging list objects are populated by magic number position, and three of the properties are strings.  There is no guarantee that the HQL query and the function to constitute the objects will be in sync.
  3. NHibernate Session handling.  I didn’t see anywhere in the code where the NHibernate session was safely closed,
  4. Catch ex Throw ex. Enough said.
  5. Too much code.  There’s too many moving parts for this to be maintainable.
  6. No tests in place.  Without automated testing in place, this is an accident waiting to happen.  As Michael Feather once said, If you don’t have a test for it, it’s wrong. (Our rewrite of this code was done test first, but I’m going to omit the tests for brevity).

Remember, the requirement is to create an IList<LoggingList> from a single table.  We could approach this in two ways.  Scrap what was there (there weren’t any tests in place anyway), or chip away. We decided to chip away since it was easier to write the tests and is more in line with the test first development/refactoring.

Using Projections in NHibernate

The first task was then to eliminate the HQL and replace that with the ICriteria style of NHibernate querying.  This requirement needed to utilize two different types of projections – RowCount and GroupProperty.  The syntax can be seen in the Final code below, line 12 (RowCount) and 14-16 (GroupProperty).

We initially called List on the criteria at this point, and received the same data back from the HQL version – and ArrayList of object[].  Better, but still smelly.

Using Transformers in NHibernate

I still needed to convert that ArrayList of object[] into a real object.  My first pass at this was to code (in the same method as the query) a for each loop to populate an IList<LoggingList>.  This worked, but still smelled.  But we were getting closer.  We eliminated two of the functions at this point, since our method with the query returned the correct list.  I thought about calling Optimus Prime…

Interestingly enough, the alias parameters for the Projections were being ignored.  This led me to do more digging, and after some research, I found the IResultTransformer interface.  I had never used it, but it looked promising. The code ended up being one line (line 18 in the final below), but there needed to be several changes to my initial Projections code.

The AliasToBean transformer (showing the Java heritage here) takes the array list of object[] generate, and will populate another class as long as the alias parameter in the Projections match property names in the class.  Once I lined them all up (case and all), everything worked like a champ.

The end result was meeting the requirement entirely through NHibernate.  Interesting stuff, no doubt.  And I certainly am glad to have added this information to my toolbox.  But will I ever use it?  I can’t see this as being the best solution to the original requirement.  However, since we had a constraint that we HAD to use NHibernate to solve this, I am confident that we met the requirement in the best possible manner.

Happy Coding!

Final Code – One Method
  1: public IList<LoggingList> GetReportObjectsByDateRange(
  2:      DateTime fromDate, DateTime toDate)

  3: {
  4:    using (ISession session = _sessionManager.GetSession())
  5:    {
  6:       try
  7:       {
  8:          ICriteria criteria = CreateCriteria(session);
  9:          //Limit the data returned
 10:          criteria.Add(Restrictions.Between("DateAccessed", fromDate, toDate));
 11:          criteria.SetProjection(Projections.ProjectionList()
 12:             .Add(Projections.RowCount(), "Count")
 13:              //Projection Type, Field for Projection, Property Name for new type
 14:             .Add(Projections.GroupProperty("UserName"), "UserName")
 15:             .Add(Projections.GroupProperty("MenuOption"), "MenuOption")
 16:             .Add(Projections.GroupProperty("RoleName"), "RoleName"));
 17:          //Transforms result to typeof Specified
 18:          criteria.SetResultTransformer(Transformers.AliasToBean(typeof (LoggingList)));
 19:          return = criteria.List<LoggingList>();
 20:       }
 21:       catch (Exception ex)
 22:       {
 23:          session.Clear();
 24:          Console.WriteLine(ex.Message);
 25:          throw;
 26:       }
 27:       finally
 28:       {
 29:          //Extension method on Session from my NHib Utils
 30:          session.SafeClose();
 31:       }
 32:    }
 33: }

Original Code
  1: Public Function LoadLoggingList(
  2:      ByVal BeginDate As Date, _ 
  3:      ByVal EndDate As Date) As IList(Of LoggingList)         
  4:    Dim datacontractLoggingList As IList(Of LoggingList) = _ 
  5:       New List(Of LoggingList)        
  6:    Dim loggingListModel As ArrayList        
  7:    loggingListModel = New ArrayList         
  8:    Dim loggingDAO As ILoggingDAO        
  9:    loggingDAO = New LoggingDAOImpl()         
 10:    Try            
 11:       loggingListModel = loggingDAO.ReadLogging(BeginDate, EndDate)            
 12:       'Dim rec As LoggingListModel            
 13:       For Each rec In loggingListModel                
 14:          datacontractLoggingList.Add(LoadLogging(rec))            
 15:       Next         
 16:    Catch ex As Exception            
 17:       Throw ex        
 18:    End Try         
 19:    Return datacontractLoggingList     
 20: End Function     
 22: Public Function ReadLogging( _ 
 23:       ByVal BeginDate As Date, _ 
 24:       ByVal EndDate As Date) As ArrayList _ 
 25:         Implements ILoggingDAO.ReadLogging        
 26:    Dim Logging As IQuery        
 27:    Dim LogList As ArrayList        
 28:    Dim session As ISession = NHibernateSessionFactory.GetSession         
 29:    Try            
 30:       Logging = session.CreateQuery("Select distinct l.MenuOption, " _ 
 31:          & "l.UserName, l.RoleName, count(*) from LoggingModel l " _ 
 32:          & "where l.CreateDate >=:BeginDate and l.CreateDate <= " _ 
 33:          & ":EndDate group by menuoption, username, rolename " _ 
 34:          & "order by menuoption, username, rolename")            
 35:       Logging.SetDateTime("BeginDate", BeginDate)            
 36:       Logging.SetDateTime("EndDate", EndDate)            
 37:       LogList = Logging.List()         
 38:    Catch ex As Exception            
 39:       Throw ex        
 40:    End Try        
 41:    Return LogList    
 42: End Function  
 44: Function LoadLogging(ByVal LoggingModel) As LoggingList        
 45: ' No Model really exists for this input record.  
 46: ' Therefore, must refer to the input fields by their 
 47: ' order in the record as opposed to their name.         
 48:    Dim datacontractLogging As LoggingList = New LoggingList        
 49:    datacontractLogging.MenuOption = LoggingModel(0)        
 50:    datacontractLogging.UserName = LoggingModel(1)        
 51:    datacontractLogging.RoleName = LoggingModel(2)        
 52:    datacontractLogging.Count = LoggingModel(3)         
 53:    Return datacontractLogging     
 54: End Function

Comments (2) -

Philip Japikse, MVP, MCSD, MCDBA, CSM

Well, that's not a simple question, since there are many tools and many different situations.  But I always strive to having a reporting version of the data store, and using a tool optimized for reporting.  For example, SQL Server Reporting Services.

You mention "Use NHibernate for CRUD.  Use {fill in the blank} for reporting."

What is your prefered {fill in the blank} to access data for reporting?

Comments are closed
Managed Windows Shared Hosting by OrcsWeb