How Can I Generate Nhibernate GROUP BY without SELECT the property

Question

I'd like to resolve that problem :

SELECT Max(Date)
FROM Table
GROUP BY SubId

(Then pass it as a SubQuery to mid-action so I can get the Id of the item in Table)

SELECT Id
FROM Table
WHERE Date in
[[[ previous request ]]]

(Then Get the full Table Item with other table join)

SELECT *
FROM Table
LEFT JOIN...
WHERE Id in
[[[ previous request ]]]

I tried this kind of request :

var subquery = QueryOver.Of<Table>(() => x)
                    .SelectList(list => list
                    .SelectMax(() => x.Date)
                    .SelectGroup(() => x.Sub.Id)
                );

                var filter = QueryOver.Of<Table>().WithSubquery.
                    WhereExists(subquery)
                    .Select(p => p.Id);

                var result = Session.QueryOver<Table>().WithSubquery.WhereProperty(p => p.Id).In(filter).Left.JoinQueryOver(p => p.Sub).List();

But the problem is that I can't get the first request right with only the date out of my request. Is there a better way to do it than that kind of subqueries ? And is there a possibility in NHibernate to Groupy By a Property without selecting it ?

Thanks !

It is possible. Check [this](http://stackoverflow.com/a/20537260/1679310) and/or [this](http://stackoverflow.com/a/25531842/1679310) — Radim Köhler, Sep 10 '14 at 17:28
Thanks for the tip. I'll try it again but for now i found a solution. — dotdiego, Sep 11 '14 at 09:06
@Revv: a possible solution here: http://stackoverflow.com/a/30915886/750216 — Răzvan Flavius Panda, Jun 23 '15 at 21:50

dotdiego · Accepted Answer · 2014-09-11T13:15:12.600

Finally did it that way and it generated the SQL i wanted. But it wasn't 3 subqueries exactly it was 3 queries looking in a set of datas (The arrays subquery and CorrespondingIds).

var subquery = Session.QueryOver<Table>(() => x)
                    .SelectList(list => list
                    .SelectMax(() => x.Date)
                    .SelectGroup(() => x.Sub.Id))
                    .List<object[]>().Select(p => p[0]).ToArray();


                var CorrespondingIds = Session.QueryOver<Table>(() => x)
                    .WhereRestrictionOn(() => x.Date).IsIn(subquery)
                    .Select(p => p.Id).List<int>().ToArray();

                var result = Session.QueryOver<Table>(() => x).WhereRestrictionOn(() => x.Id).IsIn(CorrespondingIds).Left.JoinQueryOver(p => p.Sub).List();

This is nice solution! Great.. enjoy NHibernate – Radim Köhler Sep 11 '14 at 10:02 — Radim Köhler, Sep 11 '14 at 10:02

How Can I Generate Nhibernate GROUP BY without SELECT the property

1 Answers1