Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

nHibernate many-to-many collections. (OR mapping is not one table one class)

Mapping collections in nHibernate is at first sight quite confusing. There are loads and loads of possibilities. The official documentation is somewhat academic. This is good because, once you have got it, it is a clear reference. But it is bad because the style does not always help to get it; especially when the docs start saying The previous sections are pretty confusing and try to clarify things with some short examples. Thank goodness there are more resources. Especially parts of an article nHibernate made simple by David Veeneman gave me some "aha-erlebnisse". Here I will recap parts of what I have learned and used for one of my own projects. I have picked this part as well because it does a great job in showing that OR mapping in nHibernate is more than just writing an hbm.xml mapping for every database table.

Mapping collections

There are several ways to describe a collection

  • A set contains unique items, the items have no order
  • A bag can contain the same item more than once, the items have no order
  • A list contains ordered items. Each item has an index in the list.

An nHibernate mapping of a collection can even be in four different forms. Besides these three ways there is a map. A list has an integer index; a map is a list where the index has a complex type.

These collection types map not that well on the .NET types. There is no set type in .NET and the idea of ordering is implicit. Included in the nHibernate api is Iesi.Collections which does have a set type. But nHibernate can map on just the standard .NET types. For now I will do just that. A set can be mapped to an IEnumerable the others can be mapped to an Ilist. Note that the latter implicitly introduces an order to the items in a bag.

Mapping a collection in a domain

I have a website which contains a listing of publications. These publications are categorized in a number of subjects. Every publication can be on several subjects. A clear domain model in code will look like this

namespace Gekko.Website.Domain
{
    public class Subject
    {
        public override string ToString()
        {
            return Name;
        }

        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }
}

namespace Gekko.Website.Domain
{
    public class Publication
    {
        public override string ToString()
        {
            return Title;
        }

        public virtual int Id { get; set; }
        public virtual bool Hidden { get; set; }
        public virtual string Title { get; set; }
        public virtual string Description { get; set; }
        public virtual string Url { get; set; }
        public virtual bool InDutch { get; set; }
        public virtual Publisher PublishedBy { get; set; }
        public virtual int PublishedInYear { get; set; }
        private IList onSubjects = new List();
        public virtual IList OnSubjects
        {
            get { return onSubjects; }
            set { onSubjects = value; }
        }
    }
}

This is a model I can communicate in a clear language with my customer. As stated a publication has a list of subjects.

When it comes to storing this in a relational database things are more complicated. This is a many to many relation, a publication has several subjects, a subject has several publications. To store this we need three tables. The database will look like this

The good thing about using nHibernate as an O-R mapper is that I don’t need a class for the PublicationOnSubject table. The relation between publication and subject, which is so clear in the model, can be mapped to the underlying database as a collection. The mapping of the subject is straightforward

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Gekko.Website.Domain" namespace="Gekko.Website.Domain">

  <class name="Subject" table="Subjects" proxy="Subject">

    <id name ="Id" type="Int32" column="idSubject">

      <generator class="identity"></generator>

    </id>

    <property name="Name" type="string" length="50" column="Subject"></property>

  </class>

</hibernate-mapping>

In the mapping of the publication the subjects are mapped in a many-to-many collection

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Gekko.Website.Domain" namespace="Gekko.Website.Domain">

  <class name ="Publication" table="Publications" proxy="Publication">

    <id name="Id" type="Int32" column="idPublication">

      <generator class="identity"></generator>

    </id>

    <property name="Hidden" type="boolean" column="Hidden"></property>

    <property name ="Title" type="string" length="80" column="Title"></property>

    <property name="Description" type="string" length="150" column="Description"></property>

    <property name="PublishedInYear" type="Int32" column="PublishedInYear"></property>

    <property name ="Url" type="string" length="120" column="Url"></property>

    <property name="InDutch" type="boolean" column="inDutch"></property>

    <many-to-one name="PublishedBy" class="Publisher" column="idPublisher"></many-to-one>

    <bag name="OnSubjects" table="PublicationOnSubject" lazy="false" >

      <key column="idPublication"></key>

      <many-to-many class="Subject" column="idSubject"></many-to-many>

    </bag>

  </class>

</hibernate-mapping>

OnSubjects is a bag which uses the table PublicationOnSubject. The key to this linking table is idPublication. The many to many class is the Subject class we just mapped.

Using the domain objects on a winform

The Publication and Subject domain objects are easy and clear to use in code. The OnSubjects bag of a publication maps to an Ilist and anything which implements Ilist can be used for databinding. In both classes I have overridden the ToString method to a descriptive property. This will make databinding a snap as the default display of an object is ToString().

This is some winforms code which combines controller and view. It uses the repositorymanger I presented in an earlier post. When loading the form a list of publications and a list of subjects are read from the repositories and bound directly to the controls. The listbox will display the Names of Subjects.

repository = new HibernateBasedRepository();
dataGridView1.DataSource = repository.Publications.ListAll("Title");
listBoxPotentialCategories.DataSource = repository.Subjects.ListAll("Name");

Likewise I can bind the OnSubjects property of the selected publication to another listbox

listBoxAssignedCategories.DataSource = pub.OnSubjects;

Things get even better when updating the OnSubjects property. Which is a matter of picking a Subject domain object, adding that to the OnSubjects collection of the publication and persisting the publication to the repository.

pub.OnSubjects.Add(listBoxPotentialCategories.SelectedItem as Subject);
repository.Publications.Save(pub);
repository.Commit();

Deleting a subject is a matter of removing the subject form the OnSubjects collection

pub.OnSubjects.Remove(listBoxAssignedCategories.SelectedItem as Subject);
repository.Publications.Save(pub);
repository.Commit();

The hard thing is updating the form by notifying the datasource has changed. For the sake of completeness:

((CurrencyManager) listBoxAssignedCategories.BindingContext[listBoxAssignedCategories.DataSource]).Refresh();

I can’t make that easier, that’s just winforms.

Winding down

That’s about it. We have been working with a clear model which an end user also understands. In my experience most people do understand how data in information systems is organized in tabular form. But when I start talking about normalized tables we lose each other. Here nHibernate’s O-R mapping pushes the technical details of normalization out of the model, even completely out of the code. And also for the developer writing the code work has become clearer and easier. Would you like to write out all sql needed by hand ? Not me.

This entry was posted in Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    I’m not sure if I understand your question right.

    When you delete the user the permissions will have to be deleted as well. When adding or changing permissions the underlying table is updated as well.

    You mean only your linking table is updated, the user and permissions table never are ?

    Are you using the read-only attribute in your mappings ? That would be the way to express your model.

  • Hasarinda

    Is there any way to only add the mapping to the mapping table & not to update the 2 main tables?

    I have scenario where permissions are added to users using a user-permission mapping table. Theoretically when doing this the user or the permission objects should not change.. This can be enforced in the presentation layer but is there a way to do this in the database layer using nhibernate?

  • David McClelland

    @Deepak – this is a great example of how to map an association table with properties:

    http://thesoftwaresimpleton.blogspot.com/2010/03/nhibernate-many-to-many-with-extra.html

  • Arvind

    Re-iterating PG_Kansas’ issue here :

    I have an Oracle 10G DB. The Association table has a Primary Key that is NON-NULLABLE.
    Which means to INSERT an association i NEED to generate and insert a value for the primary key as well.

    Since (as you said) in teh mapping there is no place for specifying primary key for the association – where and how do I specify or enable this? Because Oracle will not let me get away with NOT specifying the primary key on every insert to the Associations table.

    Thanks.

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    Dank je wel !

  • http://www.daaromevolutie.net/ Fedor Steeman

    This is a really REALLY very useful article. You are right: Technical documentation is important to get all the facts listed, but for getting your head around something, it can be a nightmare. This is why articles like this that cut through the jungle of details and provide the explanations that one needs to get started so great!

    Geweldig! Houe zo!

  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    Could be, but before that would be the step to include that in the domain model. As it is the subject does not have a member publications , a collection of publications on that subject, yet.

    The domain model comes first. Hibernate follows.

  • Ryan H

    Would you not add a bag, many-to-many on the Subject mapping in order to be able to pull all publications belonging to a subject also?

    Subject sub = _subjectRepos.GetSubject(Id);
    sub.Publications;

  • pg_kansas

    The idPublicationOnSubject is not getting generated for me. My back end is Oracle and hence I am using generator class=”sequence” instead of identity. Is this the reason why the column (idPublicationOnSubject) is not getting generated? Also, can anyone provide a resolution for the same?

  • http://petersgekko.codebetter.com pvanooijen

    As you can see in the diagram all three tables have a primary key. In the mapping there is no place or need for the primary key of the associaten table, idPublicationOnSubject. But it does not bite and many a database engine is happy (performs better) with at least one unique key in every physical table.

  • Lobo Jr

    The way it is mapped, lines will be inserted in the table of association?
    I believe this only works when the table of the association has only foreign keys, without primary key
    idPublicationOnSubject.

  • James Yoo

    Awesome post!!!!!!

    I’m doing the exact same thing in my app, and this is what i was looking for!

  • Laila

    Hello, nice post! It helped me out a lot! But I do have a remark.

    It’s true you can just add a subject to the collection of a presentation, and it will be correctly added to the database.
    But imagine, you want to create a new presentation and new subjects in one go, that doesn’t work here. I get an exception since nHibernate is trying to add a PublicationSubject pair to the link-table, while the Subject doesn’t exist yet.

    Example:
    Presentation p =
    new Presentation { PublishedInYear = 2008,
    OnSubjects = new List { new Subject { Name = “SomeSubject” } }
    };

    If you persist p, you will get an exception.
    Any suggestions to avoid this?

  • Adi

    @Deepak: just taking a shot here: how about using a composite element with a nested element. Read more in the NHibernate user guide, chapter 7.2. Collections of dependent objects. The exact example is in the second grey example box.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    @Deepak : The moment the table is going to contain real properties and not just id’s to link two other tables you will need to write mappings for the table.

  • Deepak

    Thanks,

    I would like to extend this scenario by placing a “status” field in the PublicationOnSubject table, which will reflect the status of the publication on a department basis.

    So, I can have a Publication with many Subjects, each subject having a different status.

    Could you please let me know how to achieve the additional status mapping here please?

    Deepak

  • Erik

    One little niggle: “There is no set type in .NET” – This is incorrect as of .NET 3.5 – see HashSethttp://msdn.microsoft.com/en-us/library/bb359438.aspx

  • http://www.tobinharris.com Tobin Harris

    Nice post – and a nice explanation of where many-many can make life easier!

    It’s worth mentioning that these join tables can be entities waiting to happen! These days I often find myself simply skipping the many to many mapping, and exposing the join as a first-class entity in the domain model (with simpler one-many associations too).