Projections.SqlProjection with NHibernate Criteria

Lateley, I have been playing with Criteria more and more. I really like what it can do. Granted, I have been using criteria and HQL for about a year and a half now to do simple stuff. However, I always felt limitied before in HQL or Criteria, due to not being able to do joins to what I wanted if I didn't have it defined as a property in my .hbm.xml file. This always led me to have to write NamedQueries or SQLQueries, which are hard to maintain, not easily readable, and as I talked about before, they bypass the cache, which means you have to be careful to call flush before calling the query.

I have now played with the Projections.SqlProjection in a criteria to get to something that I don't have defined. In the example below, I have an id, LineItemMemberID, that is not defined as an object in my hbm file, but it links to a particular table depending on what type it is. In this case, it links to the MediaProperty table. In that table, I need a column, MediaPropertyName, to pass to my DTO, BundledInventoryCartPreviewItem. This information is then used for my page that I have built and only requires me to hit the database once and get just the data that I need and none of it that I don't.

I use the {alias} tag to reference my current criteria row. What this does behind the scenes is formats it with "this_" when the parser goes through to build the query. It is a neat trick in that I have that id defined in the hbm, and then I can join off of it. Since this is a generic id in this table, meaning no Foreign Key and reusable, I was not able to link it to a specific object in the hbm. Only at the time of this query, do I know what kind of id it is.

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

   2: criteria.Add(Expression.Eq("Campaign.ID", campaignID));

   3: criteria.Add(Expression.Eq("Cart.ID", cartID));

   4: criteria.CreateAlias("PricingStructure", "bips");

   5: criteria.SetProjection(Projections.ProjectionList()

   6:            .Add(Projections.SqlProjection(@"

   7:                     (

   8:                         SELECT mp.Name AS MediaPropertyName

   9:                         FROM MediaProperty mp

  10:                             INNER JOIN CartItems ci 

  11:                                 ON ci.CartItemID = {alias}.CartItemID

  12:                                     AND ci.LineItemMemberID = mp.MediaProperty_ID

  13:                     ) AS MediaPropertyName

  14:                 ",  new string[] {"MediaPropertyName"}, new IType[] { NHibernateUtil.String}))

  15:     .Add(Projections.Property("NumberOfUnits"), "UnitCount")

  16:        .Add(Projections.Property("UnitPrice"), "UnitPrice")

  17:        .Add(Projections.Property("bips.IsDivisor"))

  18:        .Add(Projections.Property("bips.Factor"))

  19:     .Add(Projections.Property("BundleType.ID"), "BundleTypeID")

  20:     .Add(Projections.Property("ID"), "ID")

  21: );

  22: criteria.SetResultTransformer(Transformers.AliasToBean(typeof(BundledInventoryCartPreviewItem)));

  23: return criteria.List<ICartPreviewItem>();

What I found after playing with this a bit, was that we want to treat this as if it were part of a column list in the top of the sql query such as if we wanted a SUM or a MAX or a SubQuery in it. It took a few tries to get it right, I found that running it and seeing what is being created is always the trick in understanding what NHibernate is doing behind the scenes. Once you understand what is going on, you can manipulate it to do what you intend it to do.

In short, I am really starting to like using criteria as my basis to get to what I need. I have found that using the SqlProjection allows me to use little sql, instead of a lot of sql. It is easier to maintain and its a lot more readable. I hope that you all find it as helpful as I do.

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.

Welcome

Thanks and welcome to Insightful Tech. This is my first blog, so I have a lot to learn, but hopefully we can all learn together. I am hoping that this is successful.