How to map references and collections – Part 2

In the first post we have described the ways to map a foreign key reference in the database to a reference, a collection or both in memory. This post continues the topic with the ways to map a join table. A join table is necessary if an m:n relationship has to be stored, but it also can contain the ordering inside a collection or the content of a dictionary.

Let’s start with a simple join table in the database schema. Typically a join table contains two foreign key references, one to the left and one to the right table.

1) The natural way is to map this to a complete m:n association. This requires two classes, Left and Right, and two collections containing the join table information.

To generate the classes with the reverse engineering wizard (tables to classes) go to the advanced view. After selecting the ‘Join Table’ node it is possible to specify that this is an ‘Many to many’ association by setting the checkbox. The names of both lists can be specified as well.

In the previous post we had some discussions about the master and slave role if both sides are implemented. Here the ‘Owner Class’ is the master and the ‘Value Type’ is the slave.

In the forward mapping (classes to tables) the two classes Left and Right have to be implemented including their collections. In the forward mapping wizard the selected field represents the slave role. First you have to select the m:n option to specify the association type. Unselecting the ‘Preserve order’ checkbox and selecting of the ‘Managed collection’ checkbox  results in the described database schema. The join table and its column names can be specified if you scroll down in this dialog.

As you can see in the reverse mapping dialog a ‘Sequence column’ can be used as well. A sequence column can be used to preserve the order of a collection. The name of the checkbox in the forward mapping wizard is ‘Preserve order’ to express it. In the forward case you need an additional column in the join table to keep the information about the position of each entry in the master collection. If the wizard finds this entry it is mapped automatically. In forward case you have to select ‘Preserve order’.

2) The join table can also represent a ‘to n’ association. In this case only one collection is used in the model. The additional functionality that you can get compared to the foreign key mapped collections is that you can persist the order inside the collection. Also if you want to have ‘null’ entries in your collection, this is only possible with a join table in the database.

Let’s start with an additional sequence column in the model:

As you can see the primary key definition of the join table has changed. If there should be null entries for Right_ID in the table some database system does not support this as part of the PK because Right_ID is nullable.

The reverse engineering wizard detects the sequence column and the per default only one collection has been generated:

This is the default in forward case as well:

Summarizing: you can use a join table with collections in two ways, to express an m:n association or to keep the ordering in one collection.

3) There are also dictionaries in the .NET framework, what happens if you want to persist the content of a dictionary?

Let’s first look what kind of persistent dictionaries can be stored:

• Two simple types: IDictionary<int, int>

• Simple type and reference type: IDictionary<int, Value>

• Reference type and simple type: IDictionary<Key, int>

• Two reference types: IDictionary<Key, Value>

The join table definition for all four cases is similar if the class Key and Value have integer primary keys. There is an integer column for the owner of the dictionary and two integer columns for key and value. Dependent on simple or reference entry there are additional foreign key constraints. It is not possible to define a reference or collection on the opposite side.

The reverse engineering wizard is not able to detect most of the cases because it also can be a collection with sequence column. To map the dictionary you have to execute some manual steps:

• Select the join table
• Specify map as type to map to
• Specify the field name you like to generate
• Set the owner class, key type and value type
• In the ‘Field to column mapping’ grid you have to specify what is used for what
  • Id field in the Owner class
  • Id field in the Key class
  • The integer value part of the dictionary

Just try it out.