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

Don't know NHibernate well enough but it seems it doesn't translate grouping by objects well: https://stackoverflow.com/q/9054296/861716. — Gert Arnold, Aug 18 '19 at 21:36

score 1 · Answer 1 · answered Aug 18 '19 at 21:22

1

Try this:

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

Here you can find the reason for the error.

answered Aug 18 '19 at 21:22

core

851
1
8
28

Thank you, @core, but I would like to select the OtherPersistentObject itself, rather than its Id -- otherwise I'd have to make a separate call to get the object. – Michael Aug 20 '19 at 06:23

score 1 · Accepted Answer · answered Sep 29 '19 at 11:02

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

Selecting an object from the GroupBy key

2 Answers2