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

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

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

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

Coding One-to-Many Entity Associations

The most common and most important association between entities is the "One-to-Many" association. In this Part 3 of the article series we will discuss the " One-to-Many" association and reserve the "Optional One-to-Many" association for the next part. The first question would be, what's the difference between "One-to-Many" and "Optional One-to-Many"? Simply put, an optional one allows the value "0" (zero) in the multiplicity of the association. The next question would be, Is the difference essential? Absolutely yes from the ORM point of view. It's very much essential because Optional One-to-Many associations result in nullable foreign key columns and hence need to be handled differently to avoid nulls which is preferred always for DB's stored data quality.

To stress the difference between One-to-Many and Optional One-to-Many, in this article, two examples will be given. At first, an example will be shown in the e-commerce scenario for a One-to-Many association with the collection end using <set>, a scenario which would have been much better if mapped as an Optional One-to-Many association. But here it will be mapped as simply One-to-Many. So it will have many null values in the foreign key column. In the next article which is part 4 of this series, it will be improved to be mapped as an Optional One-to-Many, wherein all the nulls disappear to improve the quality and reliability of data stored. Also, another example will be shown in this article end which does not result in nulls because it is a One-to-Many association scenario and hence mapped correctly. The second example uses a <list> for the collection end.

Background

First, in part 1 of this article series, it had been shown that in ORM, while mapping a one-to-one association between objects to their corresponding tables, the primary key of one table is posted as a foreign key of the other table. But in ORM, mapping a One-to-Many association is done differently. Figure 1 shows how a One-to-Many association between objects is mapped.

Figure 1

Figure 1 shows that in ORM while mapping a One-to-Many association between objects to their respective tables, the primary key of the table in the "one" end of the One-to-Many association is posted as a foreign key to the table in the "many" end of the One-to-Many association (we will be saying the collection class end of a One-to-Many association is the "many" end throughout the article series to make it easier to describe). It is impossible to map this association another way. Why? It is easy to understand the answer if you think in terms of rows and columns of the respective table. The answer is very simple and is written below.

When we say that a One-to-Many association exists between table A and table B then what we mean in simple terms is that a row in table A will be linked with many rows in table B and in a database, this link is established between the tables by posting the primary key as a foreign key column between the tables. So if the foreign key is posted wrongly from the primary key of the "many" end table to the "one" end table, i.e from table B to table A, then, such a foreign key column value of each row in table A will have multiple values corresponding to the primary key value of each row in table B with which that particular row in table A is linked (because the association between table A and table B is One-to-Many, which means in plain simple terms, a row in table A is linked to many rows in table B). But in a Relational Database, we cannot assign more than one value to a column. Hence logically speaking, the only way to map a One-to-Many association between objects to their corresponding tables is by posting the primary key of the table in the "one" end as a foreign key to the table in the "many" end. Check this in action with the examples below using NHibernate.

Continuing With the E-commerce Scenario

So to our e-commerce scenario for an example of a One-to-Many association:

When an order is submitted for approval by a customer, the system shall check for item availability based on product descriptions given in the order. If the item is available, the item is added to the order. This is done for each product description. If an item is not available, it is added to the list of unavailable items for the order. Finally, the list of available items and unavailable items and the total money to be paid is sent to the customer. Once the Customer submits payment with the order, the items are flagged in the inventory to denote that they are ordered. The items paid by a customer are added to a PaymentApprovedOrder.

Only the last 2 sentences are of interest here as it contains a many-to-one association between Item and PaymentApprovedOrder. Both Item and PaymentApprovedOrder are entity classes. Here Item represents an item in the inventory and not the order item in the billing order as is usually done because in business you do not select a particular item to pay and take away as it's done in the real world. You just select for purchase based on product descriptions if stock is available. So we will try to capture this scenario of business. Hence we will not persist order items with Order until Payment is submitted with Order instance to the system, wherein the Order becomes a PaymentApprovedOrder and attached to the Customer who paid for it (the scenario for the second example in this article; customer and his paid orders). When Payment is made to the system, Item instances for that order in Inventory are updated by a flag to denote the Item Is Ordered after which a PaymentApprovedOrder is created with a link set to all the Item instances in that order. It is this link between PaymentApprovedOrder and Item instances our topic of discussion for the first example in this article.

Later when item delivery is made, all the items for that particular order will be removed from the inventory Items collection and added to DeliveredItems. A simple scenario for the article samples.

Back to the discussion here, so it's clear Item will have an independent lifetime, and hence its an entity. The association between PaymentApprovedOrder and Item is Optional One-to-Many. Why so? Why not just simply One-to-Many? While a PaymentApprovedOrder definitely needs to have at least one item, what is the necessity that an Item has to be ordered? It could be an Item that's never ordered in which case it's never associated with an order at all and simply exist in inventory without a sale. Hence the association between PaymentApprovedOrder and Item is Optional One-to-Many. But we will map it as simply One-to-Many, be hit with a whole lot of nulls in the database for foreign key column values, and then improve it in the next article to avoid those nulls by mapping it as Optional One-to-Many. The next example scenario in this article will be a typical One-to-Many scenario which will totally avoid these nulls.

Coding the One-to-Many

In the previous article, it was clearly shown that certain collections like <list> could preserve order information and certain collections like <set> may have no order information. In this article, it will be shown that it is very essential how the mapping must be done for collections like <list> to preserve the ordering information when it's a bidirectional association and what happens if you do not do it correctly. So this is one additional noteworthy point to be inferred from this article.

First, we will consider the example of a One-to-Many association between PaymentApprovedOrder and Item with the "many" end mapped as a <set>. Next, we will consider another example from an e-commerce scenario with <list> and see the difference in mapping to ensure that the order information captured in the <list> is stored correctly.

One-to-Many With <SET> Collection Capping

Look at Figure 2 below. It shows the One-to-Many bidirectional association's mapping between PaymentApprovedOrder and Item. The collection of Item instances is mapped using a <Set> collection. In C# code, the declaration of a collection is always by interface and will be defined by using the correct implementation C# class. Developers who have read part 1 and part 2 of the article series will know how to interpret the figure and the colored arrows are shown. If assistance is required in interpreting the figure, please read Part 1 and Part 2 of this article series.

In Figure 2 look at the three purple arrows. It shows that the column "PaymentApprovedOrderId" is the primary key of the "one" end of the table of the association i.e PaymentApprovedOrder table and is posted as a foreign-key column to the "many" end table i.e the Item table. This is as expected for mapping a One-to-Many association as was shown before while explaining the ORM fundamentals in the background section before. Since the association is bidirectional, the column PaymentApprovedOrderId is mapped twice to complete both ends of the link as can be seen in Figure 2 by following the purple arrows.

Figure 2

One end of this association is the PaymentApprovedOrder class.

public virtual ISet<Item> PaidOrderItems { get; set; }

Note: ISet<> above is from the Iesi.collections.generic namespace.

In the PaymentApprovedOrder.hbm mapping file, the preceding set is mapped as:

<set name="PaidOrderItems" inverse="true" cascade="save-update">     
 <key column="PAYMENTAPPROVEDORDERID"  not-null="true"/
  <one-to-many class="Item" />
</set>

If you compare this with the <set> mapping shown in Part 2 for value-type collections, you can see many differences that aid in understanding NHibernte mapping better. The first difference is the absence of a <element> used in value-type collections to denote the element of the collection. Next, the table is not named. NHibernate knows that we are dealing with an entity association here when it sees that there is no <element> inside <set>. Further, instead of <element> tag in <set> we use an association tag like <one-to-many>. This is a signal to NHibernate that it is dealing with an entity association and to use the table specified in the class attribute in <one-to-many> tag which in this case is class="Item". So, NHibernate will use the Item table specified in the mapping file for class Item and use the column PaymentApprovedOrderId as the foreign key column. Also, note that we explicitly specify the "cascade" attribute. Everyone is familiar with the cascade attribute from DBS and ADO.NET and the functionality is the same i.e. when PaymentApprovedOrder is saved or updated to its table then the collection denoted by <set> must also be saved or updated to its table automatically. Nothing special there, just maintaining the usual Parent/Child relationship. When the owning Parent is saved or updated, cascade the save-update function to the owned child automatically without explicitly having to do it separately for the child. But what's most interesting and the most important is not-null= true (which needs not have been there but I have included it to explain an important NHibernate mapping concept) in the foreignkey column "PaymentApprovedOrderId", which we will look at later while examing the test data and results stored in the DB.

Since this is a bidirectional association, let us look at the other side of the association.

In the Item.cs C# file, the property to store the association with PaymentApprovedOrder is as follows:

public virtual PaymentApprovedOrder PaidOrder { get; set; }

In the Item.hbm mapping file, the many-to-one association between Item & PaymentApprovedOrder is mapped as:

<many-to-one class ="PaymentApprovedOrder" name ="PaidOrder" column ="PAYMENTAPPROVEDORDERID"/>

The association between Item and PaymentApprovedOrder is mapped exactly as expected with a <many-to-one> association and the foreign key column to maintain the association s identified using the attribute, column as shown in the code snippet above.

Now have a look at Figure 3 below. It shows both ends of mapping the bidirectional One-to-Many association between PaymentApprovedOrder and Item. If you look at the <set> definition in PaymentApprovedOrder.hbm mapping file (left side of Figure 3) and the <many-to-one> association definition in the Item.hbm mapping file (right side of Figure 3), you will see that both map to the same column named PaymentApprovedOrderId shown by the orange arrow.

Figure 3

Since the association is bidirectional, the same foreign key column PaymentApprovedOrderId gets mapped twice as is shown in Figure 3. In C# code also, both ends of a bidirectional association will have to be linked. So in the PaymentApprovedOrder.cs file that defines the class PaymentApprovedOrder (shown in the code snippet below; only the relevant portions of the class are shown), have a look at the AddPaidItem(Item item) method that sets both ends of the One-to-Many association public class PaymentApprovedOrder

{
        public virtual long PaymentApprovedOrderId { get; set; }
        public virtual ISet<Item> PaidOrderItems { get; set; }
        public virtual void AddPaidItem(Item item)
        {
            //SET THE REFERENCE FOR PAYMENTAPPROVEDORDER
            //IN ITEM OBJECT TO THIS i.e THE PAYMENT APPROVED
            //ORDER INSTANCE TO WHICH THE item IS ADDED".
            // THIS IS THE FIRST END OF THE
            // ONE-TO-MANY ASSOCIATION - THE "ONE" END
            item.PaidOrder = this;
            //ADD "item" TO THE SET PaidOrderItems
            //OTHER END OF ASSOCIATION - THE "MANY" END
            PaidOrderItems.Add(item);
        }
}

So when the C# code for AddPaidItem is executed, both ends of the One-to-Many association betweenPaymentApprovedOrder and Item is established. But as was shown above in Figure 3, in the database table, both ends of the association map to the same column i.e PaymentApprovedOrderId, which is the primary key of the "one" end of the table i.e the table PaymentApprovedOrder and posted as a foreign key column in the "many" end of the table i.e. item. Having the same column mapped twice in a bidirectional association may lead to problems when NHibernate generates the automatic insert, delete, and update SQL statements because the same column will be inserted or updated twice when the link is managed in C# object code and such double changes could lead to conflicts or constraint violations or repetitions. Hence one of the ends of a bidirectional link has to be deactivated in the mapping file so that insert and update statements are not generated for both ends but only for one end. There are two ways to do it.

The first method is setting the inverse=true attribute in the collections like <set> as is shown in the left side of Figure 3. Now the collection end of the association, i.e the <set> end is indicated to be not active to NHibernate so that it will not be generating Insert and Update SQL statements automatically when changes are made to this end i.e. the collection end object link and the inverse of this, which is the "one" end (shown in the right side of Figure 3) becomes the active end for generating SQL statements automatically to correspond to changes to the object. So NHibernate will generate automatic SQL statements only when the "one" end of the link is changed.

In the AddPaidItem(Item item) method of PaymentApprovedOrder C# class, shown above in the code snippet, which establishes both ends of the link for this One-to-Many association between PaymentApprovedOrder and Item in C# code, NHibernate generates SQL statements for updates or inserts to the database only when "item.PaidOrder=this" is invoked. Nothing will happen by using the collection end i.e. the PaidOrderItems.Add(item) because it has been shown as not in active use to NHibernate for generating SQL statements automatically, by using the inverse=true attribute of <set> in the mapping file. In other words, by simply adding an instance of Item to the set collection of Item in PaymentApprovedOrder denoted by PaidOrderItems property, the item will not be added to the database. So setting PaidOrderItems.Add(item) will not add the item to the database because it has been shown to NHibernate to not use the collection end for the generation of automatic insert and update SQL statements in the collection end by using <set inverse="true"> in the mapping file. The instance of the item will be added to the database by NHibernate's automatically generated insert and update SQL statements only when the PaidOrder property of Item is set which represents the "one" end of the association. So an item is added to the database only when the item is.PaidOrder=this will be executed because only for the "one" end, NHibernate will generate automatic SQL statements for insert and update.

Thus whenever there's a bidirectional association between entity classes, to avoid any conflicts, one end of the association has to be deactivated in the NHibernate mapping file and one way of doing this is by specifying inverse=true in the mapping file in collections like <set> that do not have any ordering information. Now why can't we use inverse=true for collections that have ordering information like <list>? The answer to the question is described below and very interesting.

Why can't we use the inverse=true attribute for mapping collections that have ordering information like <list> when they are used in a bidirectional association? When we map a collection with the inverse=true attribute because the collection is used in a bidirectional association, NHibernate literally does not consider the collection defined with the attribute inverse=true of use for any further automatic DML statement generation when changes are made in the corresponding collection object in C# code. Only the other end of the association will be used by NHibernate. So it will not take <list inverse=true> mapping for further use but unfortunately, the ordering information for a <list> is encapsulated inside the <list> definition only. In other words, the ordering or index information will not be considered if the attribute inverse=true is used for collections like <list>. So the ordering information will be lost if <list inverse=true> is set. Hence bidirectional One-to-Many association mapping with <list> and other collections that have ordering information has to be handled differently which will be discussed in the next section with an example for <list>.

Figure 4 

Test Client Code

Read the test code above in Figure 4 and see the Item table rows generated by the test code in Figure 5. Look at the orange arrow in Figure 5. Figure 5 shows the <set> collection mapping for class ITEM (lower half of Figure 5) and the Item table rows generated by the test code (upper half of Figure 5). The <SET> mapping clearly says the foreign key PaymentApprovedOrderId is not null for the Item table (shown by the orange arrow). So an item cannot be added to the Item table without PaymentApprovedOrderId set to a non-null value. Yet we see in the top half of Figure 5, there are 3 rows for which the PaymentApprovedOrderId is NULL (shown by the orange arrow). How is this notnull=true constraint violation possible? Don't make the mistake of thinking this to be a side-effect of the inverse=true attribute being set. It has nothing to do with the inverse=true attribute set. The answer is not null=true should have been defined at the one end of the association also for it to have an effect instead of just in the collection mapping. In this example, the not-null=true must have been defined in the Item.hbm mapping file as shown here : <many-to-one class="PaymentApprovedOrder" name="PaidOrder" not-null=true> tag. But unfortunately, if you do that, then the constraint will fire and Item cannot be added unless it is part of an Order which is not what we want in e-commerce. We want instances of Items to exist in the inventory without an order and when an order is created, the item will have the appropriate reference set for PaymentApprovedOrder and a flag set in the item that is ordered. This is why it should have been handled as an Optional One-to-Many and not just simply One-to-Many. This clearly helps in understanding the problem if One-to-Many and Optional One-to-Many are used incorrectly.

Figure 5

To conclude this section, it's important to take a good look at the rows of the ITEM table shown in Figure 5 - Top half. See the nulls in the foreign key "PaymentApprovedOrderId"? These nulls exist because the association between PaymentApprovedOrder and Item is Optional One-to-Many (an item may exist in inventory without ever being purchased and hence with a null paid order reference to it) but it was mapped here in this example as One-to-Many to highlight this problem of null values being present in the foreignkey column. In part 4 of this article series, we will map this association between PaymentApprovedOrder and Item correctly as Optional One-to-Many, and all these nulls will disappear. Now in the next section, a One-to-Many association is mapped correctly and you will see there are no nulls in the foreign-key column because it fits that scenario correctly.

Finally, note that the first two articles have the code for IRepository, DBRepository, and Payment classes. Figure 4 shows the test code. Figure 6 shows the code and mapping file for PaymentApprovedOrder. Figure 7 shows the code and mapping file for Order.

Figure 6

 Payment Approved Order With Mapping Code

Figure 7

 Order and Its Mapping File. OrderItems are not mapped in the mapping file because it is not persisted in the database (the reason is explained earlier; we use a PaymentApprovedOrder to link the payment and order items). Such flexibility exists in NHibernate.

ONE-TO-MANY With <LIST> Collection Mapping

The <list> collection mapping has order information. Hence for a bidirectional association having <list> mapping, it is not possible to map one end of the association link by using <list inverse="true"> for reasons explained earlier. The <list> mapping has to be done differently which will be shown here with a different example from the e-commerce scenario.

"A customer will be able to know the orders for which he has made payment. Order will always identify the customer who has made the order."

Customer, Order, and PaymentApprovedOrder are all entity classes. We only make an order persistent after payment is made as a PaymentApprovedOrder with the Item collection of the order associated with PaymentApprovedOrder as shown in the example previously. A PaymentApprovedOrder is always linked to one customer but a customer in an online shop may just have a login account and not give any orders at all or can give many orders with payments. But no matter what, a paid order cannot exist without a customer. So definitely the association between Customer and PaymentApprovedOrder is One-to-Many as a PaymentApprovedOrder can be made by a customer only. Here for the "many" end collection, we will use <LIST>. Also, note that here there will not be any nulls in the foreign-key mapping because a PaymentApprovedOrder will not exist without a customer.

Figure 8 shows the Customer and PaymentApprovedOrder class and their mappings. Developers who have read Part 1 and Part 2 of the article series will know how to interpret the figure and the colored arrows shown. If assistance is required in interpreting the figure, please read Part 1 and Part 2 of this article series.

Figure 8

Have a look at the purple arrows in Figure 8. It clearly shows how the primary key CustomerId of the one end table i.e. Customer table, is posted as a foreign key for the many end or the collection end table i.e. PaymentApprovedOrder table. This is as expected for mapping a One-to-Many association as was shown before while explaining the ORM fundamentals in the background section before. Since the association is bidirectional, the column CustomerId is mapped twice to complete both ends of the link as can be seen in Figure 2 by following the purple arrows.

A customer may have many PaymentApprovedOrders (buys several items by many orders). Hence

the Customer. cs file, the collection of PaymentApprovedOrder is declared as:

public virtual IList<PaymentApprovedOrder> CustomerPaidOrders { get; set; }

In the Customer. hbm file, it is mapped as,

<list name ="CustomerPaidOrders" cascade="save-update">
 <key column ="CUSTOMERID" not-null ="true"></key>
 <list-index column ="PAIDORDER_LIST_POSITION"></list-index>
 <one-to-many class ="PaymentApprovedOrder"/>
 </list>

It has already been shown that when a bidirectional association is used, NHibernate must be informed so that it will not generate insert and update SQL statements when both ends of a bidirectional link are changed because as far as the database is concerned it is still the same foreign key column which is mapped twice to both ends of the link, to realize a bidirectional association. We saw that in <set> collections by using the inverse=true attribute (<set inverse=true...>), we could stop NHibernate from generating insert and update SQL statements when the collection end of the link is changed when collections are used in bidirectional One-to-Many association. It has also been shown that for collections with ordering information like <list>, for reasons mentioned before, the inverse=true attribute cannot be set. Hence if you look at the mapping in the code snippet above which uses the <list> collection mapping in the bidirectional One-to-Many association between Customer and PaymentApprovedOrder, inverse=true has not been set for <list>. So NHibernate has to be told in the other end, i.e the "one" end, to not generate insert and update statements automatically when changes are made to the object at that en

A PaymentApprovedOrder will be created only if a customer buys and pays for an order with items in it. Hence,

in the PaymentApprovedOrder.cs file, the Customer for a PaymentApprovedOrder is declared as.

public virtual Customer PaidByCustomer { get; set;}

In the PaymentApprovedOrder.hbm mapping file, it is mapped as.

<many-to-one name ="PaidByCustomer"  class ="Customer" column ="CUSTOMERID" not-null="true" insert="false" update="false" cascade ="save-update"></many-to-one>

The code snippet above shows the mapping of many-to-one associations between PaymentApprovedOrder and Customer. The not-null=true attribute in the above mapping is the constraint to specify that a customer must exist for a PaymentApprovedOrder. The <many-to-one> tag does not have an inverse attribute. The most interesting part is the attributes insert=false and update=false which substitute for the absence of the inverse attribute. These prevent NHibernate from generating insert and update statements automatically when the link in this end is changed using objects of this end.

By this mapping mechanism, it is ensured that the <list> is free from setting an inverse attribute and hence the complete ordering information for the list is obtained. If <list> is mapped with the inverse=true attribute, then the Ordering information or the Position of elements in the List information will be set as null.

The PaymentApprovedOrder, Customer, and Client code for testing is shown in Figure 9, Figure 10, and Figure 11. All other classes are available in Part 1 and Part 2 of the article series. Downloadable code will be available in Part 7 of this article series.

Figure 9 

Payment Approved Order Class With Mapping

Figure 10 

Customer Class With Mapping

Figure 11

Take a look at Figure 12. It shows the rows of the PaymentApprovedOrder table created by running the test client code shown in Figure 11. If you take a close look at the last column of the table in Figure 12, namely PAIDORDER_LIST_POSITION, which captures the ordering information for the list of paid orders per customer, you will see that for each customer it starts with a value of 0 to indicate the starting point of the list and gets incremented for every order added and then for the next customer it is reset to 0 to indicate the start of a new list for the next customer. Thus the ordering information for <LIST> is maintained. One other most important thing to observe in the rows of the PaymentApprovedOrder table shown in Figure 12 is, there are no null values for the foreign key CustomerId. Thus it captures the domain scenario, A PaymentApprovedOrder cannot be made without a customer. Correct One-to-Many mapping.

Figure 12

 Rows of PaymentApproved Order Table for Test Code in Figure 11

CONCLUSION

At the start of the article, it was mentioned that the One-to-Many association is very important. The reason is that the common practice of dealing with a many-to-many association in OOAD is to use an association class and break the many-to-many association into two One-to-Many associations. The next article will deal with the Optional One-to-Many Association. Enjoy NHibernate.

Reference

To gain a more comprehensive understanding of the subject please read the next part.

• Object Relational Mapping (ORM) Using NHibernate - Part 4 of 8
• Object Relational Mapping (ORM) Using NHibernate - Part 5 of 8
• Object Relational Mapping (ORM) Using NHibernate - Part 6 of 8
• Object Relational Mapping (ORM) Using NHibernate - Part 7 of 8
• Object Relational Mapping (ORM) Using NHibernate - Part 8 of 8