开发者

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>();
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜