How to retrieve row count of one-to-many relation while also including original entity?

Question

Say I have two entities Foo and Bar where Foo has-many Bar's,

class Foo {
  int ImportantNumber { get; set; }
  IEnumerable<Bar> Bars { get; set; }
}

class FooDTO {
  Foo Foo { get; set; }
  int BarCount { get; set; }
}

How can I efficiently sum up the number of Bars per Foo in a DTO using a single query, preferrably only with the Criteria interface.

I have tried any number of ways to get the original entity out of a query with ´SetProjection´ but no luck. The current theory is to do something like

SELECT 
  Foo.*, BarCounts.counts 
FROM
  Foo LEFT JOIN 
    ( SELECT fooId, COUNT(*) as counts FROM Bar GROUP BY fooId ) AS BarCounts 
    ON Foo.id=BarCounts.fooId

but with Criterias, and I just can't seem to figure out how.

Answer 1

Criteria is probably not the best API for this... HQL is easier:

var fooDTOs = session.CreateQuery(@"
              select foo as Foo,
                     count(elements(foo.Bars)) as BarCount
              from Foo foo
              group by foo.id, foo.ImportantNumber
              ")
              .SetResultTransformer(Transformers.AliasToBean<FooDTO>())
              .List<FooDTO>();