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.

amavroudakis · Accepted Answer · 2010-02-22 11:42:49Z

5

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.

answered Feb 22, 2010 at 11:42

amavroudakis

2,36217 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Conway Over a year ago

This was almost perfect, thank you!! Points awarded and I put the final solution as a new answer for anyone else coming across this question.

Chris Conway · Accepted Answer · 2010-02-22 13:25:04Z

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>();

answered Feb 22, 2010 at 13:25

Chris Conway

16.5k25 gold badges99 silver badges113 bronze badges

Comments

ValidfroM · Accepted Answer · 2011-12-11 23:17:42Z

2

DetachedCriteria is one way to do that, another way which I think is more elegant is to use LINQ.

To help someone who tried to find answer to the question in Nhibernate 3.1+ through this post like me, I will post my answer to the question here.

I am using NHibernate3.2 in which full LINQ support is available.

using NHibernate.Linq;

        var session = Application.SessionFactory.GetCurrentSession();

        var _query = from r in session.Query<Resource>()
                     orderby r.ResourceViews.Count
                     select r;
        return _query.Take(maxResults).ToList();

answered Dec 11, 2011 at 23:17

ValidfroM

2,9203 gold badges35 silver badges47 bronze badges

Collectives™ on Stack Overflow

Group By with Count using NHibernate

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related