Selecting an object from the GroupBy key

Question

I'm accustomed to GroupBy() being more of an art than a science, but maybe someone can help me with a very specific problem:

Given the following code

var results = session.Query<MyClass>()
.GroupBy(c => c.OtherPersistentObject)
.Select(group => new
{
    key = group.Key,
    count = group.Count()
})
.ToList();

The generated query comes out like this:

    /* [expression] */select
    otherclass_.ID as col_0_0_,
    cast(count(*) as INT) as col_1_0_,
    otherclass_.ID as id1_1_,
    otherclass_.START_DATE as start2_1_,
    otherclass_.END_DATE as end3_1_,
    otherclass_.Zone as zone9_1_
from
    mytable mytable0_ 
left outer join
    otherclass otherclass_ 
        on mytable0_.otherID=otherclass_.ID 
group by
    mytable0_.otherID

which gives me the SQL error "Column 'otherclass .ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Is there a way to get the Select to do what I want?

TIA

Answer 1

It's a known NHibernate issue NH-3027.

As a workaround you can use last approach described in this answer (rewrite GroupBy part as sub-query). So your query can be rewritten to something like:

var results = session.Query<MyClass>()
        .Where(c => c == session.Query<MyClass>().First(cs => cs.OtherPersistentObject == c.OtherPersistentObject))
        .Select(x => new
        {
            key = x.OtherPersistentObject, 
            count = session.Query<MyClass>().Count(cs => cs.OtherPersistentObject == x.OtherPersistentObject)
        }).ToList();