How to retrieve row count of one-to-many relation while also including original entity?
- by kaa
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.
