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:
- 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.
- 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.
- NHibernate Session handling. I didn’t see anywhere in the code where the NHibernate session was safely closed,
- Catch ex Throw ex. Enough said.
- Too much code. There’s too many moving parts for this to be maintainable.
- 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
21:
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
43:
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
55: