How to convert this HQL to DetachedCriteria?

Question

This HQL statement, when executed produces this the following result:

select t, count(s) from Submission s right join s.Topics as t GROUP BY t.Id

result[0]
    [0] topic_id, topic_name, ... 
    [1] 10

result[1]
    [0] topic_id, topic_name, ... 
    [1] 12
     .
result[n]
    [0] topic_id, topic_name, ... 
    [1] 19

This DetachedCriteria API produces almost similar result but without loading the topic

ProjectionList PrjList = Projections.ProjectionList();
PrjList.Add(Projections.GroupProperty("Topics"), "t");
PrjList.Add(Projections.Count("Id"));

DetachedCriteria Filter = DetachedCriteria.For<Submission>();
Filter.CreateCriteria("Topics", "t", JoinType.RightOuterJoin);
Filter.SetProjection(PrjList);

result[0]
    [0] null
    [1] 10

result[1]
    [0] null
    [1] 12
     .
result[n]
    [0] null
    [1] 19

For some reason nhibernate refuses to create topic objects for the result set but it does for the HQL query. Why is that?

Answer 1

Have a looked at Detached Criteria on Ayende's blog on http://ayende.com/blog/tags/nhibernate?page=3 It gives you a very detailed step by step guide on left outer joins and group by HQL aswell as the mapping behind.