Creating DTO using Criteria

I have been using NHibernate for about a year and a half now and find it is very useful. I have recently changed some of my philosophies in using NHibernate and its feature set. Some of the changes that I have made is moving to the DTO route instead of using the Domain Objects to populate grids, dropdowns, etc...

Part of the reason for this change is that Domain Objects are expensive in that you are getting back a bunch of data that you might not need, making extra calls to the database, and doing excessive joins.

In using the DTOs, I have experimented with using SQLQuery, HQL, NamedQuery, and criteria. All of them have their pros and cons in what they can or can't do. Today, I would like to discuss a criteria call to create the DTO.

In HQL and Criteria, NHibernate will hit the cache before hitting the database. In a SQLQuery or NamedQuery, the cache is not hit, therefore, the caller has to be careful and use the flush prior to executing. As we all know, flush is expensive.

Below is a criteria call:

   1: ICriteria criteria = session.CreateCriteria(typeof (PersonalInventory));

   2: criteria.CreateAlias("Inventory", "inv")

   3:     .Add(Expression.In("inv.ID", inventoryIDs));

   4: criteria.CreateAlias("Person", "p")

   5:     .CreateAlias("p.Account", "a")

   6:     .SetProjection(Projections.Distinct(

   7:         Projections.ProjectionList()

   8:             .Add(Projections.Property("p.FirstName"), "FirstName")

   9:             .Add(Projections.Property("a.ID"), "AccountID")

  10:             .Add( Projections.Property("inv.ID"), "InventoryID")

  11:         )

  12:     );

  13: criteria.SetResultTransformer(Transformers.AliasToBean(typeof (PersonalAccountInfo)));

  14: return criteria.List<InventoryMediaPropertyInfo>();

The above code is getting the Scalars FirstName, AccountID, and InventoryID. They are being set in the object PersonalAccountInfo object on the properties that match up with those names.

It is important to note that the DTO object must have a public empty constructor to do this. NHibernate will call that constructor and then set the scalars directly on the properties or field names that match. If it cannot find them, then it will complain with an insightful error.

It is also important to note that the Projections are implicitly setting the scalar for you. Normally, if it was a SQLQuery, you would need to call AddScalar for each scalar that you are bringing back from the database. Since this is making references to the mapping of the domain objects, then it can find out what type each scalar is and set it appropriately.

I have also told NHibernate to bring back only the distinct values. This is done by using Projections.Distinct which is wrapped around my list of values that I bring back.

I am joining in the criteria using the CreateAlias method call. This enables me to hook into a property on an object or alias and then go off of that. The CreateAlias method also has a join type attribute that can be set to indicate if it is a regular join, right outer join, left outer join, etc...

This criteria enables me to hit the cache and have a cleaner and much easier to maintain query. The downside is that the sql is generated and depending on how much is going on in your mapping files, then you might still get a big ugly amount of SQL flowing through. The upside is that you only get back the data that you need. I hope that you enjoyed my first technical blog.