Object Relational Mapping (ORM) Using NHibernate - Part 4 of 8

Article

Before reading this article, please go through the following articles:

Coding Optional One-To-Many Entity Associations

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 the db's stored data quality. The example shown was an optional One-To-Many association between PaymentApprovedOrder and Items in the 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 the intent is to eliminate the nulls introduced in the database by correctly mapping the association between PaymentApprovedOrder and Item as optional One-To-Many.

Background

In NHibernate, an 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 the "one" and the "many" end table set as foreignkeys in it. It avoids the null values in foreignkey posted to the "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 (which simply means that the 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 an 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.

Continuing the ECommerce Sample

The association between PaymentApprovedOrder and Item has to be mapped as an optional One-To-Many bidirectional association. We already mapped this as a simple One-To-Many association in the previous article. Now we will improve this. It is imperative to note that the best and recommended way to map collections with a 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 an 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.

First let us consider the 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 the mapping PaymentApprovedOrder class from a simple One-To-Many to Optional One-To-Many association, refer to Figure 2.

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 Part 2 and Part 3 of the article series, the 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 it's an entity association with a collection table name. The table named by <idbag table=".."> collection is called a join table and is explained in the 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 an explanation of 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 an optional One-To-Many association collection and we will discuss this more while answering the question why <idbag> is preferred over a <set> collection in mapping bidirectional optional One-To-Many associations. One of the foreign keys 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 be seen 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 occurs in the mapping file of the Item class in Item.hbm. Earlier we directly mapped the association between Item and PaymentApprovedOrder as a simple Many-To-One association. We know that in the database to realise this association link, the primary key of "one" end of the table which is "PAYMENTAPPROVEDORDERID" (from the table "PAYMENTAPPROVEDORDER") is posted as a foreignkey in the "many" end of the table i.e. the Item table (we already explained this in Part 3 of this article series - Background section). To confirm this, Refer to Figure 1 which shows the columns in the Item table and it is found that it has a column for paymentapprovedorderid, the key column posted from the paymentapprovedorder table. Now the problem is, our Item table represents items in the inventory that may not have been ordered at all. Hence this paymentapprovedorderid will have null keys. From this observation it's clear that what we need to do is to remove the nulls. We need to move this foreignkey column paymentapprovedorderid outside the table item and yet maintain the link between the paymentapprovedorder table and the item table. This is done by introducing a join table between the paymentapprovedorder table and the item table called PAYMENTAPPROVEDORDER_ITEMS table and the optional One-To-Many association between the paymentapprovedorder table and the item table is mapped to this table. Also note that the collection table named in the <idbag> collection mapping in the earlier paragraph is this same paymentapprovedorder_items join table 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 the paymentapprovedorder table) and the itemid (primarykey of the 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 the item table to the join table, there won't 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 an order i.e when a paidorder has an item bought. Let us see how this is done in the Item.hbm mapping file.

Refer to Figure 3 to see how this join table is mapped in the Item.hbm mapping file with the <join table=".."> tag shown with the oval in Figure 3 and how the Many-to-One association between item and paymentapprovedorder is pushed inside the join table shown with the orange arrow.

Item.hbm-mapping-file.jpg

Figure 3

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 keys using the tag <key..>. Just follow the orange arrow to see how the Many-to-One mapping is pushed from the 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 is creating all the null values earlier in the item table, has been pushed from the 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 the 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 a simple One-To-Many association (The same test data that was used for Part 3 of the article series, the <set> example which produces the nulls in Figure 1 is used. Note that the Part 3 <list> example used different test data. The test data is given below. Compare it with figure 1).

Figure 4 - Shows the results of an optional One-to-Many association for paymentapprovedorder and item (items in inventory). Compare this figure with Figure 1 which was full of null values.

Refer to Figure 4, the 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 the item table after shipping is done which we will see later. For the purpose of discussion, what's most interesting is that for items paid and ordered in inventory, shown by the value 1 in the isordered column of the item table (Figure 4 lower half), a row exists in the join table shown in the top half of 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 with an orange arrow.

The-PaymentApprovedOrder.cs-file.jpg

FIGURE 5

The following client test code is the same code that was earlier shown in the simple One-To-Many association in Part 3 of the article series with the <set> example. The only change is the association between customer and order that was added in the 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 the database using customer the 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 = "[email protected]" };
            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 is <idbag> preferred over the <set> collection in mapping bidirectional optional One-To-Many associations? Please refer to Figure 2. The oval in the figure shows that the collection mapping for <idbag> will have 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 that are not bought, the reference to order will be null. When these items are saved to the 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>.

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.