NHibernate| many-to-many

So I’m starting to build a proof of concept product catalog as both a reason to use NHibernate in a “real world” application but also to apply ASP.Net MVC with it as well. I chose both of these technologies because they’re supposed to create strongly built applications faster.

The following data model is the start of the catalog with more being added over the next few weeks.

The tags collection on the Product is simply an ISet<Tag>. I’ve used this rather than a list because I want to have a distinct set of tags per product. Here’s the mapping for both entities.

<class name="Product">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name"/>
  <property name="ShortName"/>
  <property name="ItemId" />
  <property name="Sku" />
  <property name="Wholesale" />
  <property name="Retail" />
  <property name="ImageUrl" />
  <property name="IsActive" />
  <set name="Tags" table="TagAssociations" cascade="all" >
    <key column="productId"/>
    <many-to-many column="tagId" class="Tag" />
  </set>
</class>

<class name="Tag">
  <id name="Id">
    <generator class="identity"/>
  </id>
  <property name="Name" unique="true"/>
</class>

Notice we’ve created an interim table to join the Tags and Products called TagAssociations (there are probably better names but meh) and done this through a many-to-many relationship on the Tags property binding it to Tag objects through the table.

From here on out our interactions with TagAssocations is nil. For example pulling products by Tag only requires the following code.

public IList<Product> GetByTag(Tag tag)
{
  using (ISession session = NHibernateHelper.OpenSession())
  {
    var list = session
      .CreateCriteria(typeof(Product), "p")
      .CreateCriteria("Tags")
      .Add(Restrictions.Eq("Id", tag.Id));
    return list.List<Product>();
  }
}

Because we’re adding in our property Tags through CreateCriteria and saying we want to pull that by Id the following TSQL is generated automatically.

SELECT this_.Id as Id3_1_, this_.Name as Name3_1_, this_.ShortName as ShortName3_1_, this_.ItemId as ItemId3_1_, this_.Sku as Sku3_1_, this_.Wholesale as Wholesale3_1_, this_.Retail as Retail3_1_, this_.ImageUrl as ImageUrl3_1_, this_.IsActive as IsActive3_1_, tags3_.productId as productId, tag1_.Id as tagId, tag1_.Id as Id5_0_, tag1_.Name as Name5_0_
FROM Product this_
inner join TagAssociations tags3_ on this_.Id=tags3_.productId inner join Tag tag1_ on tags3_.tagId=tag1_.Id
WHERE tag1_.Id = @p0; @p0 = '1'

To me this is a miracle to behold considering the shear amount of work this would take under normal circumstances.