Return List of MAX Value after group QueryOver Nhibernate

Question

Please help, Can I do this in query over nhibernate?

select max(Id) from transTable
group by PortfolioId.

I've tried this.

 var subquery = QueryOver.Of(() => q)
                       .SelectList(list => list.SelectGroup(() => q.PortfolioId))
                           .Where(Restrictions.EqProperty(
                               Projections.Property(() => p.Id),
                               Projections.Max(() => q.Id)))
                           .And(Restrictions.EqProperty(
                               Projections.Property(() => p.Id),
                               Projections.Property(() => q.Id)));

and then

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

but it doesn't work. it returns all data from the table. I just wanna get the last sequenceIDs from every user.

please help. Thanks

Answer 1

I would say, you are almost there. These are adjustments we should make, to get list of filtered items.

// group by PortfolioId
// HAVING for outer 'p.ID'
var subquery = QueryOver.Of(() => q)
    .SelectList(list => list
        .SelectGroup(() => q.PortfolioId)
        .SelectMax(() => q.Id)
    )
    .Where(Restrictions.EqProperty( // HAVING
        Projections.Property(() => p.Id),
        Projections.Max(() => q.Id)))
     ;

// now select the list of p.Id, prefiltered by above subquery
var filter = QueryOver.Of(() => p)
    .WithSubquery.WhereExists(subquery)
    .Select(Projections.Property(() => p.Id));

// finally the result as a set of q entities
// ready for paging
var result = session.QueryOver(() => q)
    .WithSubquery
        .WhereProperty(() => q.Id)
        .In(filter)
    // .Skip(0) -- paging could be used
    // .Take(25)
    .List()
    ;

Check the structure of defined SQL in this similar query: Query on HasMany reference