Location > Blog

NHibernate IQuery using a DTO class import mapping

As I had demonstrated in my previous post NHibernate SetResultTransformer and DTO using ICriteria to select across multiple entities is straight forward. At the time I got stuck on how to do this using HQL and the IQuery interface, for some queries I find HQL is more flexible. I found James Avery's blog Refactoring: Using object constructors in HQL with NHibernate he demonstrates a solution to the problem. This method uses the import mapping that allows you to reference a DTO class constructor inside your IQuery HQL.

Back to my previous example, I have a class Profile that has a many to one relationship with an class Department.

The SQL:

SELECT p.ItemID, p.Name, p.Summary, d.ItemID, d.Name
FROM Profile p inner join Department d on p.DepartmentID = d.ItemID

First I create a class to hold the result of our query. Below I have a Data Transfer Object which combines the properties from the Profile and Department classes that I require. Notice the difference from my last post I have created a constructor that takes all of the classes properties as arguments.

The ProfileDTO Class

 
    public class ProfileDTO 
    { 
        private int _itemID; 
        private int _departmentID; 
        private string _departmentName; 
        private string _name; 
        private string _summary; 
 
        public ProfileDTO() 
        { 
        } 
 
        public ProfileDTO(int itemID, int departmentID, string departmentName, string name, string summary) 
        { 
            _itemID = itemID; 
            _departmentID = departmentID; 
            _departmentName = departmentName; 
            _name = name; 
            _summary = summary; 
        } 
 
        public virtual int ItemID 
        { 
            get { return _itemID; } 
            set { _itemID = value; } 
        } 
 
        public virtual int DepartmentID 
        { 
            get { return _departmentID; } 
            set { _departmentID = value; } 
        } 
 
        public string DepartmentName 
        { 
            get { return _departmentName; } 
            set { _departmentName = value; } 
        } 
 
        public string Name 
        { 
            get { return _name; } 
            set { _name = value; } 
        } 
 
        public string Summary 
        { 
            get { return _summary; } 
            set { _summary = value; } 
        } 
    } 
 

NHbernate Import Mapping Definition

NOTE: If your mapping file contains class mappings as well as the import mapping put the import mapping first, initially I had them the other way round and the example would not run.

 
  <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> 
    <import class="Juna.GraduateProfileDTO,Juna"/> 
  </hibernate-mapping> 
 

Create the HQL

The ProfileDTO constructor is included in the HQ, you must of course have the arguments in the correct order.

 
    public static IList<ProfileDTO> List() 
    { 
        StringBuilder sql = new StringBuilder(); 
        sql.Append("select new ProfileDTO(p.ItemID, p.Name, p.Summary, d.ItemID, d.Name "); 
        sql.Append("from Profile p join p.Department as d "); 
        sql.Append("order by p.Name"); 
        IQuery criteriaSelect = Persistence.Session.CreateQuery(sql.ToString()); 
        return criteriaSelect.List<ProfileDTO>(); 
    } 
 

Using a DTO class makes manpulating your results much easier, previously I would have had to enumerate the resulting object[] to get the results, this can get confusing trying to work out what column you are working with.
Saturday February 14 2009 10:46 a.m.