Oh yeah, I have a blog; maybe I should post something once in a while.
The Problem...
So, at work I finally had an opportunity to use NHibernate on a little project. This project is a very simple data retrieval service. The service will only ever read from the data store; it will never write to it. Because of the sensitive nature of the data, we are required to use views that were built specifically for our project. The views do not contain any unique identifiers. Below is an example of a details view we would have. This details view contains personal information about a person; their name, phone number, address, etc. We also have an identifier for each person; however, the identifier is not unique in the view. The person can be listed multiple times for each year they are registered.
The problem started when I used NHibernate to pull back records for a person who was registered more than 1 year. Below is the result set I expected back from NHibernate:
| Identifier |
FirstName |
LastName |
PhoneNumber |
RegisteredYear |
| 12345 |
Mary |
Smith |
555-1234 |
2007 |
| 12345 |
Mary |
Jones |
555-5678 |
2008 |
| 12345 |
Sara |
Jones |
555-5678 |
2009 |
But what I actually get back from NHibernate is 3 Person objects, all with duplicate data like so:
| Identifier |
FirstName |
LastName |
PhoneNumber |
RegisteredYear |
| 12345 |
Mary |
Smith |
555-1234 |
2007 |
| 12345 |
Mary |
Smith |
555-1234 |
2007 |
| 12345 |
Mary |
Smith |
555-1234 |
2007 |
When I discovered this, my first thought was that NHibernate is doing some sort of caching under the hood. Then, for some reason, because the person identifier matches on the next results, it just returns a list of the same object multiple times. So, being VERY new to NHibernate, I googled about turning caching off. I found a thread mentioning to use an IStatelessSession instead of an ISession. I did that; didn't fix it. Below is an example of the (very simple) HQL query that I'm doing (yes it's VB, another requirement of this project).
Dim session As IStatelessSession = NHibernateHelper.OpenStatelessSession()
Return session.CreateQuery("from Person p where p.Identifier = :id") _
.SetString("id", identifier).List(Of Person)()
Again, I am certainly an NHibernate n00b. I'm starting to really love the tool, but I am learning that there is a bit of a learning curve when it comes to the more advanced features. So at this point I was quite stumped. Google was now failing me, and I was starting to think about scraping NHibernate and just doing straight ADO. As a last ditch effort, I reached out to a circle of developers that have much more experience with this fantastic ORM.
I got good tips from a few guys, which helped me learn a bit of what NHibernate is doing, but they still weren't fixing my problem. One of the suggestions was telling me I had cartesian products in a non lazy collection. However, this couldn't be the case as I did not have any collections in these objects. None of the views that we were accessing reference any other views or tables so there are no joins being built by NHibernate. Finally, I received a response from James Kovacs who explained the problem quite well:
"As far as I can tell, the problem isn't Cartesian products, but NHibernate's identity map (aka 1st level cache). When loading an object from a resultset, NH first checks whether it already has the ID in its identity map. If so, it uses that object. Otherwise it creates a new one and sticks it in the identity map. This avoids update problems and circular reference problems."
"Update problem: If we didn't have the identity map... Load the same Customer twice. Get back two different customer objects. Update each customer object and save. What is in the database? Last update wins."
"Circular ref problem: If we didn't have the identity map... Load a Customer. Customer contains an Order. Load the Order. Guess what! It contains a Customer. Which contains an Order. Which contains a Customer... Lather, rinse, repeat."
"The 1st level cache is tied to the session. When the session is closed, the first level cache goes away. So you need to map the PersonInfo with a composite key - whatever makes it unique."
The Solution...
So, I don't have a unique primary key in the view, but NHibernate needs a unique key. So the solution to my problem was, as James mentioned, to use a composite key. Luckily, in this scenario, I could make a composite key. Each record for a person is only in the view once for each registered year. So I needed to make a composite key that is the person identifier and registered year.
In this project, I am also using Fluent NHibernate. If you have not tried FN yet, you owe it to yourself to give it a run. Get rid of your XML mappings! To my pleasant surprise, FN supports composite keys. Below is the class map for the Person object (VB lambdas aren't as pretty as the C# syntax).
Public Class PersonMap
Inherits ClassMap(Of Person)
Public Sub New()
WithTable("personinfo_view")
UseCompositeId() _
.WithKeyProperty(Function(x As Person) x.Identifier, "pers_id") _
.WithKeyProperty(Function(x As Person) x.RegisteredYear, "reg_year")
Map(Function(x As Person) x.FirstName).TheColumnNameIs("pers_firstname")
Map(Function(x As Person) x.LastName).TheColumnNameIs("pers_lastname")
Map(Function(x As Person) x.PhoneNumber).TheColumnNameIs("pers_phone")
End Sub
End Class
After adding in the composite key, NHibernate returned back the results I was expecting. Luckily this isn't a normal problem as most of the time you will have a unique id.
I hope this helps someone else.