How to map many to many association in NHibernate

Question

I have some database tables named "Project", "Employee" and "Branch". An employee can work simultaneously on more than one project. Similarly, in a project, there are multiple employees. Also, a project is conducted at a particular branch. To maintain all these relationships, I am using a "project_employee_branch" table, which will store the related primary keys of the above three tables. As an example, this "project_employee_branch" table may contain a row like (1,2,3), which means the project whose primary key is 1, is conducted at branch whose primary key is 3, and one of its project member is an employee whose primary key is 2.

How can I map all these associations in NHibernate? I have mapped many-to-one association using foreign key concept, but I don't know how to map these types of associations, where an intermediate table is involved.

I really need some urgent advice :(

Answer 1

First point I'd make is that your database schema and your description don't match, so please take any advice below in the light of that initial caveat. You say that

a project is conducted at a particular branch

which implies there should be a simple foreign key relationship from project to branch. And of course, if this is what the schema looked like, you would have a two-way many-to-many link table and your life would be much easier.

Anyway, with the three-way combination you have, you need to have a collection of components, where the components have many-to-one properties for the other two object types. There is an example in section 7.2 of the NHibernate documentation, but I think it would look something like this in the mapping for Product:

<set name="BranchEmployees" table="product_employee_branch" lazy="true">
    <key column="product_id">
    <composite-element class="Purchase">
        <many-to-one name="Branch" class="Branch" />
        <many-to-one name="Employee" class="Employee"/>
    </composite-element>
</set>