Getting Count when using DetachedCriteria/DistinctRootEntity

Question

update: After not getting anywhere with this, I noticed that when I pulled the queries out into two distinct criteria, the actual hydrated objects returned were correct, but the count query was returning the joined dataset.

So the question is: How do I use Projections.RowCount in conjunction with CriteriaUtil.DistinctRootEntity and a DetachedCriteria.

I have two entities that are linked via a many to many - Tags and Posts.

For an admin screen, I would like to return paged results, so I'm using a multicriteria to pull back my objects, as well as the counts:

var multiResults = session.CreateMultiCriteria()
    .Add(criteria.SetMaxResults(Top))
    .Add(clone.SetProjection(Projections.RowCount())).List();

One of the filters that I have on this page is a DateLastUsed filter. Here, I want to return a Tag only if it has an associated post that that was posted after a certain date.

criteria.CreateAlias("AssociatedPosts", "p");
var mostRecent = DetachedCriteria.For(typeof (Post),"p2").SetProjection(Property.ForName("CreatedDate").Max())
                .Add(Restrictions.EqProperty("p.id", "p2.id"));

criteria.Add(Subqueries.Le(postedAfterDate),

mostRecent));

This seems to return the correct data set, but I'm returning a tag for each post that it is associated with. The next step was to apply criteria.SetResultTransformer(CriteriaUtil.DistinctRootEntity) but this didn't seem to resolve the issue.

I saw these couple of links that seemed to point towards a bug, but it seems that its been fixed (we're using 2.0.1.4000).

• NH-1055 - Multi Criteria ignored Result Transformer
• http://markmail.org/message/rp3qusnhwcdincfo

Any ideas? First time I've played with MultiCriteria, so I could be doing something else weird (the other filters are trivial).

Answer 1

After doing some further investigation, I stumbled upon this:

http://floledermann.blogspot.com/2007/10/solving-hibernate-criterias-distinct.html

The issue described is almost identical to the one I'm running into. The comments provide some good additional insight.