How to retrieve row count of one-to-many relation while also including original entity?
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开发者_如何学Python 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.
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>();
精彩评论