Fetch Tags and Tag count using HQL on SQL Server 2008

Question

I'm implementing tagging on a particular entity, using NHibernate on SQL Server 2008. The structure I have now is, simplifying, like this:

public class Entity {
    public Guid Id { get; set; }
}

public class Tag {
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public class TagAssoc {
    public Tag LinkedTag { get; set; }
    public Entity LinkedEntity { get; set; }
    //User
    //Other properties
}

Nothing exotic: an entity can be tagged multiple times with the same tag, since the association also includes data about the user that tagged the entity and other stuff.

Now, I'm trying to fetch a list of tags of a particular entity, with the counts of how many times the tag has been applied. Something like this in HQL:

select tass.LinkedTag, count(tass.LinkedTag)
from TagAssoc as tass left outer join tass.LinkedTag as t
group by tass.LinkedTag

This generates the following SQL query:

select tag1_.Id as Id0_, tag1_.Name as Name0_, tag1_.Id as x0_0_, count_big(tag1_.Id) as x1_0_
from BandTags tagassoc0_ left outer join Tags tag1_ on tagassoc0_.TagId=tag1_.Id
group by tag1_.Id

This looks correct, but won't work in SQL Server 2008, because the Name property of Tag is not included in a "group by" clause. To make it work, I have to manually adjust the group by clause in order to include all properties of the Tag class:

select tass.LinkedTag, count(tass.LinkedTag)
from TagAssoc as tass left outer join tass.LinkedTag as t
group by tass.LinkedTag.Id, tass.LinkedTag.Name

But this depends on the properties of the Tag class and therefore would have to be updated every time the class is changed. Is there some other way to make the first HQL query work? Perhaps some HQL syntax that automatically makes the "group by" properties explicit?

Thanks

Answer 1

It doesn't appear that there is any way to make NHibernate determine the group by properties automatically. The documentation even seems to imply this in the example HQL they give for an aggregate function:

select cat, count( elements(cat.Kittens) ) from Eg.Cat cat group by cat.Id, cat.Weight, ...

There they also explicitly specify the properties of Cat.

If you want to dynamically build a query that does not need an update every time the class changes, I think you're stuck with Reflection and the Criteria interface.

ProjectionList list = Projections.ProjectionList();
foreach (PropertyInfo prop in typeof(Tag).GetProperties())
{
    list.Add(Projections.GroupProperty(prop.Name));
}
list.Add(Projections.Property("LinkedTag"));
list.Add(Projections.Count("LinkedTag"));

session.CreateCriteria(typeof(TagAssoc)).SetProjection(list).List();

I haven't tried this so it may or may not work or might need some tweaking, but you get the idea. You might decide the Tag class won't change enough to be worth the trouble.