Lately I have been exploring NHibernate and ICriteria. NHibernate is great I would recommend it to anyone but often I find the documentation and examples are hard to follow.
I found an interesting article by Max Andersen on Criteria Transformers for Hibernate but of course it applies to NHibernate as well. I tried it out and it worked well.
It’s really easy with NHibernate to select a simple typed list of results
For example:
| |
| public static IList List() |
| { |
| ICriteria criteria = Persistence.Session.CreateCriteria(typeof(Profile)); |
| return criteria.List(); |
| } |
| |
But how about when your HQL or ICriteria selects across multiple classes or entities. For example you may have an class Profile that has a many to one relationship with an class Department.
Your SQL would be like:
SELECT p.ItemID, p.Name, p.Summary, d.ItemID, d.Name
FROM Profile p inner join Department d on p.DepartmentID = d.ItemID
First we need to create a class to hold the result of our query. Below I have created a DTO or Data Transfer Object which combines the properties from the Profile and Department classes that I require.
| |
| public class ProfileDTO |
| { |
| private int _itemID; |
| private int _departmentID; |
| private string _departmentName; |
| private string _name; |
| private string _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; } |
| } |
| } |
| |
From here we can create a query that returns a Generic list of
ProfileDTO.
| |
| public static IList List() |
| { |
| ICriteria criteriaSelect = Persistence.Session.CreateCriteria(typeof(Profile)); |
| |
| criteriaSelect.CreateAlias("Department", "d"); |
| |
| criteriaSelect.SetProjection( |
| Projections.ProjectionList() |
| .Add(Projections.Property("ItemID"), "ItemID") |
| .Add(Projections.Property("Name"), "Name") |
| .Add(Projections.Property("Summary"), "Summary") |
| .Add(Projections.Property("d.ItemID"), "DepartmentID") |
| .Add(Projections.Property("d.Name"), "DepartmentName")); |
| |
| criteriaSelect.AddOrder(Order.Asc("Name")); |
| criteriaSelect.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(ProfileDTO))); |
| return criteriaSelect.List(); |
| } |
| |
NHibernate SetResultTransformer combined with the generic list make returning a typed list from a complicated query really easy.