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.