Group By with Count using NHibernate

nhibernate

Solution

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.

Problem

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.

Original source