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
“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