Nhibernate group by - select latest row for each foreign key

Question

I'm having difficulties with NHibernate and a simple GROUP BY expression.

I have a table with records of user activity. I need to return the latest activity row for each user, sorted by Time descending, which also corresponds to descending IDs (autoincrement).

Basically, this is the SQL I want to run:

SELECT * FROM log_user_activity GROUP BY UserID DESC

I searched SO for an similar question and got a bunch of answers with projections, subqueries, DTOs...

Is there any simple way this can be done, without defining a new DTO class, or using a projection with anonymous object where I have to manually specify all the columns again? A solution using QueryOver syntax would be preferable, but not mandatory.

score 1 · Accepted Answer · edited May 23 '17 at 12:07

Is there any simple way this can be done, without defining a new DTO class, or using a projection with anonymous object where I have to manually specify all the columns again?

No, there is no other way, then:

Select a mapped object as is or
Select a projection - which could but does not have to be transformed into original or DTO object (we can always return .List<object[]>()...)

But what we can do, is to profit from 16.8. Subqueries, and return just a list of mapped object as they are, filtered by these with the max Time property:

ActivityLog activity = null;

// subquery to be later used for EXISTS
var maxSubquery = QueryOver.Of<ActivityLog>()
    .SelectList(l => l
        .SelectGroup(item => item.UserID)
        .SelectMax(item => item.Time)
    )
    // WHERE Clause
    .Where(x => x.UserID == activity.UserID )
    // HAVING Clause
    .Where(Restrictions.EqProperty(
        Projections.Max<ActivityLog>(item => item.Time),
        Projections.Property(() => activity.Time)
    ));

// final query without any transformations/projections... but filtered
var result = session.QueryOver<ActivityLog>(() => activity)
    .WithSubquery
    .WhereExists(maxSubquery)
    .List<ActivityLog>()
    ;

Also check these:

Query on HasMany reference
custom result transformer could be interesting for other cases

Nhibernate group by - select latest row for each foreign key

1 Answers1