NHibernate Many-to-many with Count

Question

I have entities Post and Tag, where there is a many-to-many relationship between the two (e.g. each post can have one or more tags, and each tag can be associated with any number of posts).

What I would like to achieve is to have the Tag entity to have a field providing the number of posts which are associated with that tag. However, I don't know how to go about getting this without getting the entire collection of Posts (which I'd like to avoid).

I am using Fluent NHibernate and my entities and mappings look like this currently:

Entities/Post.cs

public class Post : PersistentBase
{
    public virtual string Title { get; set; }
    /* snip */

    private IList<Tag> tags = new List<Tag>();
    public virtual IEnumerable<Tag> Tags {
        get { return tags; }
    }

    public virtual void AddTag(Tag tag) {
        this.tags.Add(tag);
    }
}

Mappings/PostMap.cs

public class PostMap : ClassMap<Post>
{
    public PostMap()
    {
        Id(x => x.Id).GeneratedBy.HiLo("99");
        Map(x => x.Title);
        /* snip */
        HasManyToMany(x => x.Tags);
    }
}

Entities/Tag.cs

public class Tag : PersistentBase
{
    public virtual string Name { get; set; }

    public static Tag Create(string name) {
        return new Tag { Name = name };
    }
}

Mappings/TagMap.cs

public class TagMap : ClassMap<Tag>
{
    public TagMap ()
    {
        Id(x => x.Id).GeneratedBy.HiLo("99");
        Map(x => x.Name).Unique();
    }
}

Ideally what I'd like to achieve is to be able to add into Entities/Tag.cs, something like the following:

public virtual int PostCount { get; set; }

And then have that pre-filled with the number of posts using that tag.

How might I go about doing this? (is it indeed possible?)

Answer 1

What you need is to define a formula in your Tag mapping.

Here is how the Tag entity mapping should look like in XML (I am sure you can translate that to the Fluent NHibernate way):

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" ...>
    <class name="Tag" table="Tag">
     <id name="Id" type="Int32" column="TagID">
      <generator class="hilo" />
     </id>

        ...

     <property name="Name" column="Name" type="String" not-null="true" />
     <property name="PostCount" formula="(select count(*) from PostTag pt where pt.TagID = TagID)" type="Int32" insert="false" update="false" />

     ...
    </class> 
</hibernate-mapping>

This is assuming that your association table between Post and Tag in your Database is called PostTag. Please change the name accordingly to suit your needs.

Formulas work on the database level so are written in SQL and not HQL therefore the column names are used instead of the property names as you will notice in the XML above.

Then as you described you will need in the Tag.cs to add aproperty like this:

public virtual int PostCount { get; protected set; }