NHibernate Projections

As I have mentioned before prior to my rebirth as an agile developer I used to spend a lot of time obsessing about tuning SQL queries. With a new perspective and greater experience I now consider a much of the time that I spent doing this as wasted. This is primarily because I now recognize the pitfalls of premature optimization.

In case you haven’t already guessed I’ll let you into a little secret – I’m a bit of an NHibernate fan. And one of the hardest things for me personally when moving from database-centric to domain-centric development was to accept imperfection in the SQL generated by ORM tools. But 80% of the time the standard mappings are sufficient for 95%+ of applications. And judicious use of HQL and ISQLQuery can increase that percentage significantly.

Today I had to build a drop down that required a whole bunch of object graph navigation to fill. I needed only 2 columns out of approximately 50 spread across multiple tables. Something buried deep inside me woke up and screamed “No”. I didn’t really need to here but how might I do this more efficiently?

There are multiple techniques that can be employed in this scenario. Two approaches that I have used successfully in the past are custom mappings and DTOs. I would like to focus on the latter here. I am going to leverage a feature is only hinted at in the documentation (check the last example in section 11.4).

I’m going to use a simplified example for the purposes of illustration. The drop down needs to contain Inspectors and the data that I need to build the name is in a different table to the ID. I started by creating the DTO (please excuse the formatting – WordPress is bastardizing the output from CopyAsHTML):


public class InspectorDropDownDTO
{
private string _inspectorID;
private string _fullName;
public InspectorDropDownDTO(long inspectorID, string firstName, string lastName)
{
_inspectorID = inspectorID.ToString();
_fullName = lastName + ", " + firstName;
public string InspectorID { get { return _inspectorID; } set { _inspectorID = value; } }
public string FullName { get { return _fullName; } set { _fullName = value; } }
}


I need a mapping file but it’s incredibly simple (all of the DTO mappings can be found in DTOImports.hbm.xml):


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<import class="ABF.Domain.DTO.InspectorDropDownDTO, ABF.Domain">
</hibernate-mapping>


Finally I need to create a repository method to populate the DTO:


public class InspectorRepository
{
public List GetInspectorDropDownDTO()
{
IQuery q = session.CreateQuery("select new InspectorDropDownDTO(i.ID, p.FirstName, p.LastName) from Inspector i join i.Person p");
return (List)q.List();
}
}


My inner demon is silenced…for now.