NHibernate and Mapping Aggregates

A few days ago a friend emailed me the following question regarding NHibernate mappings for a solution he’s currently developing:

“I have an idea entity that has a collection of comment entities and I need to get the comment count for each idea. I made a massive mistake at the beginning by calling idea.Comments.Count (even worse, I did it in the view!), which due to the collection being lazy-loaded caused about 10 database calls so performance was sluggish even with second level cache.  I was therefore wondering how you would do it - would you use HQL and use Comments.size or would you do something differently?”

Now, I’ve been pretty busy recently, so before I had opportunity to respond properly, he sent this follow-up:

"After looking for a solution for getting a Comment count back for each Idea, I found using the Nhibernate Formula method does the job - just wanting to make sure I was on the right track in terms of performance etc.  My mapping class is as follows:"

public class IdeaMap : ClassMap<Idea>
{
   public IdeaMap()
   {
       Id(x => x.Id)
           .Column("ID")
          .GeneratedBy.Identity();
       Map(x => x.Summary).Not.Nullable();
       Map(x => x.Description).WithMaxSize().Not.Nullable();
       Map(x => x.Created).Not.Nullable();
       Map(x => x.LastStatusChange).Not.Nullable();
       Map(x => x.Visible).Not.Nullable();
       Map(x => x.Status).Not.Nullable();

       Map(x => x.CommentCount)
           .Formula("(select count(*) from Comment c where c.IdeaId = ID)");

       HasMany(x => x.Votes).KeyColumn("IdeaId")
           .Inverse().Cascade.AllDeleteOrphan();
       HasMany(x => x.Comments).KeyColumn("IdeaId")
           .Cascade.All();
       References(x => x.Category).Column("CategoryId")
           .Not.Nullable();
       References(x => x.CreatedBy).Column("UserId")
           .Not.Nullable();
       Cache.ReadWrite();
   }
}

Read more: Ian Nelson

Posted via email from .NET Info