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

Article series

• Part 1
• Part 2
• Part 4
• Part 5A
• Part 5B
• Part 6
• Part 7

Introduction

The most common and the 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 is 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 an ORM point of view. It is very much essential because optional one-to-many associations result in nullable foreign key columns and hence needs to be handled differently to avoid nulls which are always preferred 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. First an example will be shown in the ecommerce 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 a simply one-to-many. So it will have a whole lot of 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 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

Firstly in Part 1 of this article series, it was been shown that in an ORM, while mapping a one to one association between objects to their corresponding tables, the primarykey 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 shows that in ORM, while mapping a one to many association between objects to their respective tables, the primarykey 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 one-to-many association as the "many" end throughout the article series to make it easier to describe). It is impossible to map this association other 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 "one" end as a foreign key to the table in the "many" end. Check this in action with the examples below using NHibernate.

Using the code

Continuing with the e-commerce scenario

So to our ecommerce scenario for an example of a one-to-many association: "When an order is submitted for approval by customer, the system checks 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 total money to be paid is sent to the customer. Once the Customer submits payment with 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 two 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 e-business you do not select a particular item to pay and takeaway like it is done in real world. You just select an item for purchase based on product descriptions if stock is available. So we will try to capture this scenario of e-business. Hence we will not persist order items with Order until payment is submitted with the 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 first example in this article. Later when items 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 it's an entity. The association between  PaymentApprovedOrder and Item is an 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 is never ordered in which case it's never associated with an order at all and simply exists in the inventory without a sale. Hence the association between PaymentApprovedOrder and Item is an Optional One-to-Many. But we will map it as a simple One-to-Many, 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 an 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 is 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 the e-commerce scenario with <list> and see the difference in mapping to ensure that the order information captured in <list> is stored correctly.

One-to-Many with <SET> collection mapping

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 coloured arrows shown. If assistance is required on 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 primarykey of the "one" end table of the association i.e., PAYMENTAPPROVEDORDER table and is posted as a foreign key column to the "many" end table, i.e., 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.

One end of this association is the PaymentApprovedOrder class. In the PaymentApprocedOrder.cs C# file, the property, set of Items is declared as follows:

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

Note - ISet<> above is from the Iesi.collections.generic namespace. In the PaymentApprovedOrder.hbm mapping file, the above 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 valuetype collections, you can see a lot of differences that aid in understanding NHibernte mapping better. First difference is the absence of <element> used in valuetype 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 the <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 a <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 foreignkey column. Also note that we explicitly specify the "cascade" attribute. Everyone is familiar with cascade attribute from DBs and ADO.NET and the functionality is 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 whats most interesting and most important is not-null= true (which need not have been there  but i have put it to explain an important NHibernate mapping concept) in the foreignkey column "PAYMENTAPPROVEDORDERID", which we will look later while examining 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 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 is identified using the attribute, column as shown in the code snippet above. Now have a look at Figure3 below. It shows both the 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 the Figure 3) and the <many-to-one> association definition in the Item.hbm mapping file  (right side of the Figure 3), you will see that both map to the same column named PAYMENTAPPROVEDORDERID shown by orange arrow.

Since the association is bidirectional, the same foreignkey 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 PaymentApprovedOrder.cs file that defines the class PaymentApprovedOrder (shown in the code snippet below - only relevant portions of class is 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 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 between PaymentApprovedOrder and Item is established. But as was shown above with Figure 3, in the database table, both ends of the association maps to the same column i.e., PAYMENTAPPROVEDORDERID, which is the primary key of the "ONE" end table i.e the table PAYMENTAPPROVEDORDER and posted as a foreignkey column in the "MANY" end 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 end 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 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 right side of Figure 3) becomes the active end for generating SQL statements automatically to correspond to changes to 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 end 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 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 statement automatically, by using the Inverse=true attribute of <set> in the mapping file. This means by simply adding an instance of Item to the set collection of Item in PaymentApprovedOrder denoted by PaidOrderItems property, the item will not get added to database. So setting PaidOrderItems.Add(item) will not add the item to 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 collection end by using <set inverse="true"> in mapping file. The instance of 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 database only when item.PaidOrder=this will be executed because only for the "ONE" end, NHibernate will generate automatic SQL statements for insert and update.

Thus whenever there is a bidirectional association between entity classes, to avoid any conflicts, one end of the association has to be deactivated in 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 cant we use INVERSE=TRUE for collections that have ordering information like <list>? The answer to the question is described below and very interesting.

Why cant we use 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 inverse=true attribute because the collection is used in a bidirectional association, NHibernate literally does not consider the collection defined with 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 Nhiberante. So it will not take <list inverse=true> mapping for further use but unfortunately the ordering information for a <list> is encapsulated inside <list> definition only. This means 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 next section with an example for <list>.

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 foreignkey PAYMENTAPPROVEDORDERID IS NOT NULL for ITEM table (shown by the orange arrow). So a ITEM  cannot be added to the ITEM Table without PAYMENTAPPROVEDORDERID set to a non null value. Yet we see in the top half of the Figure 5, there are three 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 sideffect of inverse=true attribue being set. Its got nothing to do with 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 effect instead of just in 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 a Order which is not what we want in ecommerce scenario. We want instances of Item to exist in inventory without a order and when order is made, the item will have the appropriate reference set for PaymentApprovedOrder and a flag set in the item that its ordered. This is why it should have been handled as 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 is used wrongly.

To conclude this section, its 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 ( a item may exist in inventory without ever being bought and hence with null paidorder 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 foreignkey column because it fits that scenario correctly.

Finally, note that first two articles have the code for IRepository, DBRepository, 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. 

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 ecommerce scenario. "A customer will be able to know the orders for which he has made payment. A order will always indentify 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 to PaymentApprovedOrder as shown in the example previously. A PaymentApprovedOrder is always linked to one customer but a customer in 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 paidorder 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 foreignkey mapping because a PaymentApprovedOrder will not be existing 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 on interpreting the Figure, please read part 1 and part 2 of this article series.

Have a look at the purple arrows in the Figure 8. It clearly shows how the primarykey 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, in 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 has to 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 database is concerned it is still the same foreignkey column which is mapped twice to both ends of link, to realise 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 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 end. 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 association 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 a inverse attribute. The most interesting part are the attributes insert=false and update=false which substitute for the absence of 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 inverse attribute and hence the complete ordering information for the list is got. If <list> is mapped with inverse=true attribute, then the Ordering information or the Position of elements in List information will be set as null. The PaymentApprovedOrder, Customer and client code for testing is shown in Figure 9, Figure 10, 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.

Take a look at Figure 12. It shows the rows of 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 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 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.

Points of Interest

At the start of the article it was mentioned that One-to-Many association is very important. The reason is because, the common practice of dealing with 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 Optional One-to-Many association after which we will discuss the Many-To-Many association. Enjoy NHibernate.

• Part 1
• Part 2
• Part 4
• Part 5 A
• Part 5 B
• Part 6
• Part 7