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 ON
Foo.id=BarCounts.fooId
but with Criterias, and I just can't seem to figure out how.
I posted the same question over at stackoverflow.com where I got the
following suggestion, using HQL,
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>();
My problem with this is that my actual Foo has a lot of properties and
maintaining that is a bit of a worry. Can I somehow write something
closer to my original SQL query?
Any help would be appreciated.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
