The purpose of this topic is to provide a detailed information about what will be visible in the Mapping Information view, when you select an array of reference types in the treeview and choose m:n (many to many) relationship.
When you select an array of business objects in the treeview and the relationship is m:n (many to many), then the Mapping Information view should look like the snapshot below:
The following options are available:
Foreign Key Constraints - sets the name of the referential integrity constraint for the field. The following options are currently available:
- Transient (skip this field) - check this if the field should not be stored in the database.
- Serialize to Blob - check this if the field's value should be stored serialized into a Blob column.
- Field behavior - defines the field behavior. It can be readwrite, readonly or insertonly. The default is readwrite.
- Item Type - the type of the array elements. In case of collection of similar types this option is disabled.
- Relationship - chooses the relationship kind. It can be 1:n (one to many), m:n (many to many) or join table. In this tutorial is demonstrated the situation when the relationship is m:n.
- Inverse field - this is the most specific characteristic in this case. This is the collection field on the other side. The collection member of the referenced class will need to be specified here.
||Note that in order to implement a many to many relationship between two objects you must have a collection field in both of the objects. E.g. suppose you have to implement m:n relationship between the Category and Product objects. For that purpose you need to have an IList<Product> field in the Category object and respectively an IList<Category> field in the Product object.|
- Cascading delete - in case of cascading deletes, if a "Delete" occurs, the referenced object(s) will also be deleted (including their dependant referenced objects...). The [OpenAccess.Depend] attribute is added to the referenced persistent class or to the persistent values in a collection or array.
- Preserve order - check this box to preserve the order of the added elements. This preserves the index of each element by adding a sequence column in the join table.
- Default fetch group - check this box to include the referenced items into the default fetchgroup.
- Managed collection - check this box to automatically update the inverse reference collection, while inserting or removing an object into or from the referenced collection. This option is enabled only if the m:n relationship is checked. That means it is disabled in the current case.
- Prefetch algorithm - set the type of join used to fetch the referenced instance (no, outer or inner). This can be used to solve the "N+1" database calls problem.
- Prefetch with Outer join - use an outer join to retrieve fields.
- Prefetch with Inner join - use an inner join to retrieve fields. Using an inner join allows the join to extend further across the object graph as other joins can be done from the joined table. However, rows will be lost if the reference contains null or the database referential integrity is invalid.
||Be careful while using inner joins because rows may be lost from queries.|
- Prefetch with No join - do not join to retrieve the fields.
Join table - a join table holds the value for each element in the array map. OpenAccess allows you to modify the properties of the join table as well.
- Generate with default name - a constraint with default name will be generated.
- Do not generate constraint - if you choose this option, then no constraint will be generated.
- Generate with custom name - if you choose this option, then a constraint with a custom name will be generated. Note that there is an additional text box, where you should specify the custom constraint name.
Name - contains the name of the selected column in the Columns combo box. OpenAccess gives you the ability to edit each column name at your discretion.
Type - contains a drop-down list with the supported database datatypes.
SQL Type - contains a list with the supported SQL types for the selected database.
Length - sets the length (or precision) of the database column.
Scale - sets the scale (digits after the decimal point).
- Table name - contains the name of the join table, which can be edited.
- Create/Update table - check this box, to automatically update the join table, while inserting an object into one of the referenced collections.
- Columns - this contains a list of the columns of the Join table. The available columns are shown on the image below:
- val - valueCol - the first column in the Join table is the value column. Its name is "val".
- category_id - ownerPkCol - this is the primary key column. In this case its name will be "category_id".
- seq - sequenceCol - the sequence column. In this case its name will be "seq".