Three way many-to-many relationship

MichelG · **Joined:** Fri Sep 29, 2006 4:54 am **Posts:** 6

Hello,

This might be an obvious question to experienced users, but this is new to me, and I can't seem to find it in the documentation or in the forums.

I have four tables, lets call them A, B, C and X.
X is an association table for a many-to-many relationship between A, B and C.

X has got a column for each association: referenceA, referenceB and referenceC.

A, B and C each have their associated classes ClassA, ClassB and ClassC.

In my *.hbm.xml files, that looks like this:

Code:

<class name="ClassA, assemblyName" table="A">
   <id name="Id" column="AID" ...>
   <property ... />
   ...
</class>
<class name="ClassB, assemblyName" table="B">
   <id name="Id" column="BID" ...>
   <property ... />
   ...
</class>
<class name="ClassC, assemblyName" table="C">
   <id name="Id" column="CID" ...>
   <property ... />
   ...
</class>

I want to be able to do this:

Code:

ClassA a = GetAByWhateverMeans();
IList instancesOfB = a.InstancesOfB;
foreach(ClassB instanceOfB in instancesOfB)
{
   Console.Writeline(instanceOfB.Id);
   foreach(ClassC instanceOfC in instanceOfB.InstancesOfC)
   {
      Console.Writeline(instanceOfC.Id);
   }
}

But what I want form this instanceOfC is only instances that apply to A!

Now of course comes my question: how do I map this association?
In a two way association that's easy:

Code:

<bag name="InstancesOfB" table="X">
   <key column="referenceA"/>
   <many-to-many class="ClassB, assemblyName" column="referenceB"/>
</bag>

I could declare such an InstancesOfB and InstancesOfC for ClassA. Then a InstancesOfA and InstancesOfC for class B. Finally InstancesOfA and InstancesOfB for ClassC.

Is this the way to go? Is there a better alternative, like creating a ClassX to manage these associations? Do I not run the risk of having circular references?

Any help would be appreciated.

Note:
NHibernate version:1.0.2.0

MichelG · **Joined:** Fri Sep 29, 2006 4:54 am **Posts:** 6

Another way to look at it would be this:

Code:

ClassA a = GetAByWhateverMeans();
ClassB b = GetBByWhateverMeans();
ClassB c = GetCByWhateverMeans();

IList instancesOfC1 = a.GetInstancesOfC(b);
IList instancesOfC2 = b.GetInstancesOfC(a);

IList instancesOfA1 = b.GetInstancesOfC(c);
IList instancesOfA2 = c.GetInstancesOfC(b);

IList instancesOfB1 = a.GetInstancesOfC(c);
IList instancesOfB2 = c.GetInstancesOfC(a);

subdigital · **Posted:** Fri Sep 29, 2006 9:49 am

The trick to mapping complex associations is to map them as actual entities. Unless you have a plain-jane many-to-many between 2 tables (and no other data related/joined to the association) then you'll often have to do this.

For example, I have Users and Roles. the UserRole table needed to have some data attached to it.

so my entities are:
User (maps to [user] table)
Role (maps to [role] table)
RoleAssignment (maps to [userrole] table)

then I have a one-to-many between Users and RoleAssignments.

You should be able to adapt this to work with your 3-way many-to-many.