Search code examples
c#nhibernateormgroup-byqueryover

Nhibernate group by - select latest row for each foreign key

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.

Solution

  • 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:

    1. Select a mapped object as is or
    2. 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: