Group By with Count using NHibernate

Question

Given the following tables:

Resources:
ID int,
Name varchar(100),
Address varchar(500),
City varchar(100),
etc.

ResourceViews:
Id int,
resourceId int,
userId int,
viewDate DateTime

every time a resource is looked at, an entry is added to the ResourceView table for that user.

Here are the corresponding classes:

public class Resource
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public IList<ResourceView> ResourceViews { get; set; } // simplified
    etc. -- class simplified for question
}
public class ResourceView {
    public int Id { get; set; }
    public Resource Resource { get; set; }
    public User User { get; set; }
    public DateTime ViewDate { get; set; }
}

Using NHibernate, how can I get the top 5 most viewed resources in order by the count similar to what the following sql retrieves:

select * from [resource]
where ID in (
select top 5 resourceId from resourceViews
where userId = 3
group by (resourceId)
order by count(*) desc
)

Bonus points if it can be done with Criteria API instead of hql.

Answer 1

Try this:

DetachedCriteria dcriteria = DetachedCriteria.For<ResourceView>("rv")
                        .Add(Restrictions.Eq("userId", 3))
                        .SetProjection(Projections.GroupProperty("rv.PostID"))
                        .AddOrder(Order.Desc(Projections.Count("rv.Id")))
                        .SetMaxResults(5);

var results = NHibernateSessionManager.Session.CreateCriteria<Resource>("r")
                            .Add(Subqueries.PropertyIn("r.Id", dcriteria))
                            .List<Resource>();

The generated SQL looks exactly like the one you have on your question. Hence I believe it is what you are looking for.

Answer 2

tolism7 had it 99% of the way there, here's the final solution for anyone else with a similar problem.

var dcriteria = DetachedCriteria.For<ResourceView>("rv")
                .Add(Restrictions.Eq("User", user))
                .SetProjection(Projections.GroupProperty("rv.Resource"))
                .AddOrder(Order.Desc(Projections.Count("rv.Id")))
                .SetMaxResults(maxResults);
var results = Session.CreateCriteria<Resource>("r")
                .Add(Subqueries.PropertyIn("r.Id", dcriteria))
                .List<Resource>();