Object Relational Mapping (ORM) using NHibernate - Part 4 of 8 Coding Optional One-to-Many Entity Associations

Introduction

• PART 1
• PART 2
• PART 3
• PART 5 A
• PART 5 B
• PART 6
• PART 7

It was explained in the previous article (part 3 of the article series) that an optional one-to-many allows the value '0' (zero) in the multiplicity of the association. It was also shown with an example that optional one-to-many associations results in nullable foreign key columns if not mapped correctly and hence needs to be handled differently to avoid nulls which is preferred always for db's stored data quality. The example shown was an optional one to many association between PaymentApprovedOrder and Items in inventory which was mapped simply as One-To-Many thereby resulting in null values in the ForeignKeyValue. Figure 1 shows the Item Table with null values for the foreignkey PAYMENTAPPROVEDORDERID caused by mapping the optional One-To-Many association between PaymentApprovedOrder and Item as simply One-To-Many.

FIGURE 1-RESULTS OF ONE-TO-MANY ASSOCIATION FOR PAYMENTAPPROVEDORDER AND ITEM (ITEMS IN INVENTORY) - SEE THE NULL VALUES IN FOREIGN KEY COLUMN

Here in this article it is aimed to wipe out the nulls introduced in the database by correctly mapping the association between PaymentApprovedOrder and Item as optional One-To-Many.

Background

In NHibernate, Optional One-To-Many association is correctly mapped by introducing a JOIN TABLE between the "ONE" end table and "MANY" end table. A row in the join table will have the primarykey of both "ONE" and "MANY" end table set as foreignkeys in it. It avoids the null values in foreignkey posted to "MANY" end table found earlier by moving the foreignkey to the join table. So will the null value for the ForeignKey Column now occur in the join table? Definitely Not. In Nhibernate you define the Join Table by saying that a row in the join table is OPTIONAL (simply means row is absent for any null value foreignkey) i.e if a row is present in the join table then both foreignkeys are REQUIRED and present. Abstractly reading this may not be enlightening. After reading the example, read this paragraph to enjoy the beauty of how NHibernate join tables can be used to solve the problem of null values in mapping optional one-to-many bidirectional association. The abstract concept is most important and hence reading this paragraph again after finishing the sample is better to truly enjoy NHibernate.

Using the code

CONTINUING THE ECOMMERCE SAMPLE

The association between PaymentApprovedOrder and Item has to be mapped as optional one-to-many bidirectional association. We already mapped this as simply one-to-many association in previous article. Now we will improve this. It is imperative to note that the best and recommended way to map collections with bidirectional association is to use collections like <set> and <idbag> . Ordered collections like <list> are best avoided for collections in bidirectional association. It so happens for a optional one-to-many association, the best collection to use is <idbag>. We will see why <idbag> is preferred than <set>. The <idbag> in c# code will be declared using "IList<>" but one should remember <idbag> does not store ordering information or index information.

Firstly let us consider PaymentApprovedOrder end of the association. An instance of PaymentApprovedOrder will have one or many instances of Item. The c# code is changed in PaymentApprovedOrder.cs so that the declaration for the collection is IList<Item> to map it to <idbag> in the mapping file. But to know the changes in mapping PaymentApprovedOrder class from simply One-To-Many to Optional One-to-Many association, Refer to FIGURE 2.

In figure 2 (lower side - optional one-to-many mapping), the most notable change apart from the change of collection mapping to <idbag> from <set> is in the way the collection <idbag> is mapped. Here the collection table is named for an entity association. Remember from part2 and part3 of the article series, collection table is named only for valuetypes but for entity associations the collection table is implied by NHibernate from the association. Also here there is no <element> tag inside the <idbag> collection but it has an association specified as <many-to-many> signifying its an entity association with collection table name. The table named by <idbag table=".."> collection is called JOIN TABLE and is explained in next paragraph very clearly. The <many-to-many> assocition mapping is necessary instead of <many-to-one> mapping because JOIN TABLE does not work as required with <many-to-one> mapping. The <many-to-many> mapped is constrained to work like a <many-to-one> association by using the attribute unique=true as in <many-to-one unique="true">. For explanation on how this works, refer to Part 1 of this article series where a <many-to-one> was constrained to behave like a <one-to-one> using the unique=true attribute. The most important property worth noting is that <idbag> defines a separate surrogate key for the collection using the tag <collection-id>. This primarykey column in <idbag> is what makes it ideal for use in optional one-to-many association collection and we will discuss more on this while answering the question why <idbag> is preferred over <set> collection in mapping bidirectional optional one-to-many associations. One of the foreign key for the collection table from this end of the association is named using the tag <key> and here it is "PAYMENTAPPROVEDORDERID". This is one end of the association where the collection is defined with <idbag> and a jointable which we will see in detail next on the other side of the association.

Let us consider the other end of the association, i.e Item class. Refer to Figure 3. A major change in mapping happens in the mapping file of Item class in Item.hbm. Earlier we directly mapped the association between Item and PaymentApprovedOrder as simple Many-To-One association. We know that in database to realise this association link, the primary key of "ONE" end table which is "PAYMENTAPPROVEDORDERID" (from table "PAYMENTAPPROVEDORDER") is posted as foreignkey in "MANY" end table i.e ITEM table (we already explained this in part3 of this article series - Background section). To confirm this, Refer to figure 1 which shows the columns in ITEM table and it is found that it has a column for PAYMENTAPPROVEDORDERID, the key column posted from PAYMENTAPPROVEDORDER table. Now the problem is, our ITEM table represents items in inventory that may not have been ordered at all. Hence this PAYMENTAPPROVEDORDERID will have null keys. From this observation its clear what we need to do to remove the nulls? We need to move this foreignkey column PAYMENTAPPROVEDORDERID outside the table ITEM and yet maintain the link betweenPAYMENTAPPROVEDORDER table and ITEM table. This is done by introducing a JOIN TABLE between PAYMENTAPPROVEDORDER table and ITEM table called PAYMENTAPPROVEDORDER_ITEMS table and the optional one-to-many association between PAYMENTAPPROVEDORDER table and ITEM table is mapped to this table. Also note that the collection table named in <idbag> collection mapping in the earlier paragraph is this same PAYMENTAPPROVEDORDER_ITEMS jointable only. The columns in the jointable will be the foreignkey posted from the primarykey of the tables linked with the one-to-many association. Hence in our case the join table will have the foreignkeys set to PAYMENTAPPROVEDORDERID (primarykey of PAYMENTAPPROVEDORDER table) and ITEMID (primarykey of ITEM table. A row in this jointable will denote the item bought for a particular paymentapprovedorder. Since the foreignkey column that was causing the null has been moved from ITEM table to the join table, there wont be any nulls in the ITEM table. The most interesting thing is that the JOIN table itself will not have any null values and will have a row only when a item is bought in a order i.e when a paidorder has an item bought. Let us see how this is done in Item.hbm mapping file.

Refer to figure 3 to see how this JOIN TABLE is mapped in the Item.hbm mapping file with <join table=".."> tag shown with the oval in figure3 and how the many-to-one association between ITEM and PAYMENTAPPROVEDORDER is pushed inside the join table shown with the orange arrow.

Look at figure 3. The light turquoise oval shows the join table mapping. As usual since the association is bidirectional, one end has to be made inverse and the join end is chosen here. The Join table will have two foreign key columns - ITEMID and PAYMENTAPPROVEDORDERID. The ITEMID is set as one of the foreign key using the tag <key..>. Just follow the ORANGE arrow to see how the many-to-one mapping is pushed from ITEM table to the JOIN table. The column PAYMENTAPPROVEDORDERID named here in this <many-to-one> mapping becomes the other foreignkey (the collection side also names as foreign key column).

So now the foreignkey column PAYMENTAPPROVEDORDERID creating all the null values earlier in ITEM table, has been pushed from ITEM table to the join table PAYMENTAPPROVEDORDER_ITEMS table along with ITEMID of items bought for a particular PAYMENTAPPROVEDORDER. So how does the jointable avoid the null values? In figure 3, the mapping for the join table shown with a light turquoise oval shows an attribute optional=true set for the join table as in <join optional="true" table="...">. The attribute optional="true" means a row is added to this join table only when the columns are non-null. If a null value exists for a column of the row to be added to jointable, then in setting <join option="true"...>, that row with a null value is not added. Hence the JOIN TABLE is kept free from nulls . More correctly the join table maps the domain scenario like this: A row is added to the join table only when it is an item ordered and paid in a payment approved order. Else the item will only exist in inventory and not in the join table which should have only paid and bought items. This can be seen in Figure 4 very clearly which shows the ITEM table and the join table PAYMENTAPPROVEDORDER_ITEMS table without any null values for the same test data used for that in FIGURE 1, which had a whole lot of nulls when mapping was done with simple one-to-many association (The same test data that was used for Part 3 of the article series - <set> example which produces the nulls in figure 1 is used. Not that Part 3 <list> example used different test data. The test data is given below. Compare it with figure 1).

Refer to figure 4 lower side that shows the ITEM table. The second column, ISORDERED denotes the boolean value set when an item is ordered and paid for. These items will be removed from ITEM table after shipping is done which we will see later. For the topic of discussion whats most interesting is that for items paid and ordered in inventory, shown by the value 1 in ISORDERED column of ITEM table (figure 4 lower half), a row exists in the JOIN TABLE shown in top half of the figure 4 indicating the ITEM ordered and the corresponding PAYMENTAPPROVEDORDER of the item. Also note that there are no null values in both the tables unlike Figure 1.

The PaymentApprovedOrder.cs file is shown in Figure 5. The bag collection declaration in c# code is shown in orange arrow.

The client test code below is the same code that was earlier shown in the simple one-to-many association in part3 of the article series with <set> example. The only change is the association between customer and order that was added in previous article. But the test data is the same. The results produced in both cases was shown earlier and is useful to see how mapping a optional one-to-many correctly avoids null columns. Note that to show cascading in full action, we save all the persistent instances to database using customer repository which will cascade to PaymentApprovedOrder,Payment,Item etc. Other ways are also possible because we use bidirectional association with cascades set correctly to show parent-child relationships.

//TEST CLIENT CODE
IRepository<Item> items_repo = new DBRepository<Item>();
IRepository<PaymentApprovedOrder> paid_orders_repo = new DBRepository<PaymentApprovedOrder>();
IRepository<Customer> customer_repo = new DBRepository<Customer>();
//CREATE 7 NEW ITEMS
Item[] items = new Item[7];
items[0] = new Item { InventorySerialCode = "00A0110" };
items[1] = new Item { InventorySerialCode = "01A0101" };
items[2] = new Item { InventorySerialCode = "02A10101" };
items[3] = new Item { InventorySerialCode = "03A01010" };
items[4] = new Item { InventorySerialCode = "04A101010" };
items[5] = new Item { InventorySerialCode = "05A010101" };
items[6] = new Item { InventorySerialCode = "06A0100100" };
//ADD LAST FIVE ITEMS TO REPSITORY
//ITEMS ADDED HAVE SERIAL CODE 03--- to 07---
//ALL THESE ITEMS WILL HAVE NULL FOR PAYMENTAPPROVEDORDER REFERENCE
//BECAUSE THEY EXIST BEFORE IN INVENTORY AND NOT BOUGHT
for (int counter = 3; counter < items.Length; counter++)
{
    items_repo.addItem(items[counter]);
}
// CREATE AN ORDER
Order order = new Order();
//ADD FIRST THREE ITEMS TO ORDER
//IN ACTUAL SCENARIOS ALL ITEMS WILL
//BE EXISTING IN REPOSITORY (DB)
//EVEN BEFORE A ORDER IS PLACED
//BUT JUST TO SHOW CASCADE ATTRIBUTE IN
//ACTION, THEY HAVE NOT BEEN ADDED BEFORE.
order.OrderItems.Add(items[0]);
order.OrderItems.Add(items[1]);
order.OrderItems.Add(items[2]);
//ADD ONE MORE ITEM TO ORDER THAT WAS ALREADY SAVED IN REPOSITORY
//THIS IS THE CORRECT WAY. ITEMS EXIST IN REPOSITORY EVEN
//BEFORE A ORDER IS CREATED.
order.OrderItems.Add(items[3]);
//// Add Customer for the Order
Email mail1 = new Email { EmailAddress = 
  "<a href="mailto:alice@wonderland.com">alice@wonderland.com" };
Customer customer = new Customer { CustomerName = "AliceWonder", EmailIdentity = mail1 };
order.OrderedByCustomer = customer;       
//CREATE A PAYMENT
Payment payment = new Payment { PaymentAmount = 1000 };
//CREATE A PAYMENT APPROVED ORDER
PaymentApprovedOrder paid_order = new PaymentApprovedOrder(order, payment);
// SAVE PAYMENTAPPROVEDORDER etc TO DB using CUSTOMER
//ALL ITEMS NOT IN DB WILL BE SAVED TO DB
//BECAUSE CASCADE IS SET . 
customer_repo.addItem(customer);

Finally, why <idbag> is preferred over the <set> collection in mapping bidirectional optional one-to-many associations? Please refer to the figure 2. The oval in the figure shows that the collection mapping for <idbag> will be having a surrogate key set as primary key for the collection table. This is why it is preferred over a <set> collection in mapping optional one-to-many association. The <set> mapping does not have this surrogate key and further to adhere to the definition of set that all items in it are unique, the <set> will create a composite primary key using the columns in the table which in our join table is both the foreign key columns, i.e ITEMID and PAYMENTAPPROVEDORDERID. So both these columns must be mapped as not-null. But for items in inventory not bought, the reference to order will be null. When these items are saved to database (for example while adding a new item to inventory), a property exception will be raised saying that a notnull property is referenced by the instance being saved, because the reference to order, is null. Hence to avoid this, a straight forward <bag> collection is preferred for Optional One-to-Many association. Thus bidirectional Optional One-To-Many association is mapped correctly with <idbag>.

Points of Interest

CONCLUSION

It must be noted that though the <idbag> collection is mapped with list in c# code, the position information will not be captured for bag (mostly will not make a difference for entity bidirectional associations). But<idbag> is apt for correct optional one-to-many bidirectional association mapping. The next article of this series will be on many-to-many association.

• PART 1
• PART 2
• PART 3
• PART 5 A
• PART 5 B
• PART 6
• PART 7