Like this:

I’ve been working through the Summer of Nhibernate screen casts by Steve Bohlen. I have to take my hat off to Steve, these are brilliant, they are so informative covering many aspects of Nhibernate…and I’m only up to Session 3!

The last example on the Session 2a screen cast demonstrates grouping using HQL and mapping the output to a class which isn’t persisted in the database. Great, works like a treat though I did make the mistake of using CreateCriteria rather than CreateQuery with the HQL statement and went around in circles for half an hour, the exception message I’d received was “No persister for: select new….“

Anyway, Steve ended the session there but hadn’t shown the equivalent method using the Criteria API. I checked out the blog for the sessions and although someone had posted a sort of solution, it wasn’t complete. A working solution is below (beware of my naming conventions if you’re looking to paste the code).

The first thing to do was to create a projection list for the fields you wanted to return giving them an alias,

FirstName will be given the alias “FirstName”

count(FirstName) will be given the alias “Count”  (use Projections.Count to get the count function in SQL)

You also need a projection to group on FirstName using Projections.GroupProperty, supplying the name of the field you want to group on which is FirstName. You’ll get an informative error coming back from the database if you don’t put the group by in.

Finally you need to tell Nhibernate how to map the results into your CustomerFirstNameCounter class. To do this you use SetResultTransformer and supply the target type.

Being an NHibernate novice I’d be grateful for any feedback on ways to improve the code.

return session.CreateCriteria(typeof (Customer))
  .SetProjection(Projections.ProjectionList()
      .Add(Projections.Property("FirstName"), "FirstName")
      .Add(Projections.Count("FirstName"), "Count")
      .Add(Projections.GroupProperty("FirstName")))
      .SetResultTransformer(Transformers
            .AliasToBean(typeof(CustomerFirstNameCounter)))
  .List<CustomerFirstNameCounter>();

Advertisement