How to query for most commonly used many-to-one in nhibernate

Question

I have the following issue in the project I am working on. Each transaction in the system is assigned to a given user. So there is a many to one relationship between transactions and users, like so:

public class User 
{
    public int ID { get; private set; } 
    public string FirstName { get; set; }
    ....
}

public class Transaction 
{
    public int ID { get; private set; }
    public User CreatedBy { get; private set; } 
    ...
}

I have mapped these entities with NHibernate so that there is a many-to-one mapping between the Transaction and the User classes. The User object doesn't have a list of transactions, but the Transaction has a reference to the User that created it.

Now I want to query to retrieve a list of the users who created the most transactions, but I can't figure out how to get the top 10 most referenced users using NHibernate.

Any ideas? I would like to be able to use ICriteria to complete this rather than HQL, but HQL would be ok if required.

Update

I tried sirrocco's suggestion with the query as...

DetachedCriteria topReferencedUsers = DetatchedCriteria.For(typeof(Transaction)) 
    .SetProjection(Projections.GroupProperty("CreatedBy.Id"))
    .SetProjection(Projections.Count("CreatedBy.Id").As("pcount" ))
    .AddOrder(Order.Desc("pcount"))
    .SetMaxResults(10);

and build that as the subquery...

GetSession().CreateCriteria(typeof (User))
    .Add(Subqueries.PropertyIn("Id", topReferencedUsers))
    .List<User>();

but this subquery does not group but returns the total number of transactions, which are then used as the IN clause to the User query. If I add the ProjectionList() with both projections, I get the output of the subquery that I want, but it fails because it tries to run the two column output into the IN clause of the User query. How do I get NHibernate to project both the ID and the Count, but only join on the ID?

Update (2)

I tried Sirrocco's SqlGroupProjection suggestion (thank you Sirrocco) but came up empty. First it gave me errors saying that it couldn't find the property pcount, which meant that I needed to remove the order by, which means it was ordering by some timestamp, which won't work. But even with that, it is still only outputing the count of the times that the user was referenced, not the user id with which to join the Users table. Any ideas? Thanks.

Answer 1

You could split the operation into two steps.

1) Execute topReferencedUsers, then extract the CreatedBy.Id projection into an int array in memory (since you're only dealing with 10).

2) Then Execute:

GetSession().CreateCriteria(typeof(User))
    .Add(Expression.InG<int>("Id", topTenIdArray))
    .List<User>();

Answer 2

You can try it for yourself and see if you get the desired output.

var userIds = this.Session
    .CreateQuery(@"
select a.User.Id 
from Transaction as a 
group by a.User 
order by count(a.User) desc")
    .SetMaxResults(10)
    .List<int>().ToArray();

var users = this.Session.CreateCriteria(typeof(User))
    .Add(Restrictions.InG("Id", userIds))
    .List<Artist>();

return users;

The userId's that I get from the first queries are (90,22,50,55) but when passed to the second one I get my users in 22,50,55,90 order.