NHibernate Criteria with multiple results

Using HQL, you can build a query like the following which will return an array of objects for each row selected by simply using a comma delimited list in the select clause:

select mother, offspr, mate.Name from Eg.DomesticCat as mother inner join mother.Mate as mate left outer join mother.Kittens as offspr

How would you do this with an ICriteria? Specifically, I am trying to build a paged query which also returns the total row count in the same query.

Ayende shows how you can do this with HQL (http://ayende.com/Blog/archive/2007/04/27/Paged-data--Count-with-NHibernate-The-really-easy-way.aspx). I need to replicate this using an ICriteria.

From stackoverflow

• Ayende also added Multi Criteria support: http://ayende.com/Blog/archive/2007/05/20/NHibernate-Multi-Criteria.aspx. That's in the 2.0 release, apparently.

  Jon Hynes : That could be useful, but in my case I have a complicated select statement that I don't want to run twice. There's got to be a way to do it in a single statement, just have another column for COUNT(*) OVER(x) AS TotalRows

• I would presume that the answer lies in prefetching the associations that you want to. That way you can fetch the needed part of your object graph in a single shot. You would do this in criteria queries like so.

  ICriteria query = session.CreateCriteria(typeof (Cat))
  .SetFetchMode("Mate", FetchMode.Join)
  .SetFetchMode("Kittens", FetchMode.Join);
  
  IList<Cat> results = query.List<Cat>();
  

  This will give you back a list of cats with both the Mate and the Kittens prepopulated. You can then navigate to these properties without incurring an N+1 penalty. If you need a more flattened result I'd do it using linq, perhaps like this.

  var results = query.List<Cat>()
    .Select((c) => new {mother = c, mate = c.Mate, offspr = c.Kittens});
  

  This will give you back a flattened list of anonymous types with the given properties. This will work if all you need is prefetching the object graph. However if you need to prefetch things such as counts or sums then you will need to examine the Projections and Alias parts of Criteria queries.

  One more thing. If you are trying to exactly duplicate your above query, you can do so like this.

  ICriteria query = session.CreateCriteria(typeof (Cat), "mother")
  .CreateAlias("Mate", "mate", JoinType.InnerJoin)
  .CreateAlias("Kittens", "offspr", JoinType.LeftOuterJoin)
  .SetResultTransformer(Transformers.AliasToEntityMap);
  

  This will return you basically the same as your hql query, however instead of using an indexed list it will use a dictionary that maps the alias to the entity.

• You can also do paging by manipulating the resultset on the Criteria:

  ICriteria query = session.CreateCriteria(typeof (Cat), "mother")

  query.SetFirstResult(x); // x = page start

  query.SetMaxResults(y); // y = page size

  List results = query.List();

• You can convert your criteria to a count criteria by this way

  DetachedCriteria countCriteria = CriteriaTransformer.TransformToRowCount(criteria); // criteria = your criteria
  rowCount = countCriteria.GetExecutableCriteria(session).UniqueResult<int>();