Author: Sebastijan Pistotnik, member of NConstruct Developers Team
Release date: March, 2008

NConstruct and ORM in Adventure Works database

This article discusses:

OR mapping issues with NHibernate and NConstruct on randomly chosen database
Using composed key mapping in NConstruct
Problems with fetching several “parallel” collections – Cartesian product problem

Contents:

SalesOrderDetail table OR mapping issues
EmployeeDepartmentHistory table OR mapping issues
BillOfMaterials table OR mapping issues
WorkOrderRouting table OR mapping issues
Product table OR mapping issues

Adventure Works is an enterprise class schema (relational database) and sample data for a fictitious sporting goods manufacturer. It can be found and downloaded from the following URL:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=8392

In our process of creating an object/relational mapping, we found some mapping issues with the following database tables:

1 SalesOrderDetail table OR mapping issues

1.1 Table details (Figure 1)

Figure 1

Note Similar relations can be found on table PurchaseOrderDetail.

1.2 Composite primary key and Autonumber primary key problem

The unusual thing here is that along with SalesOrderDetailID, which is an Autonumber (Identity) primary key, there is also another primary key SalesOrderID. This would in normal circumstances give us composed primary key.
Since this could present a problem in NHibernate mapping and while editing such an entity, we created the following mapping rule: “Whenever there are several primary keys and any of them is an Autonumber key, a single (not composed) unique identifier mapping which has a native generator is created.”

<id name="Id" column="SalesOrderDetailID" type="System.Int32" unsaved-value="null">
	<generator class="native"/> 
</id>

Also the other primary key in the above case must be a many-to-one association with its updatable property set to true.

<many-to-one name="SalesOrder" access="field.pascalcase-underscore" not-null="true" 
	outer-join="auto" insert="true" update="true"> 
	<column name="SalesOrderID"/> 
</many-to-one>

So the edit form for specific class entity is as follows in Figure 2.

Figure 2

1.3 Foreign keys to composite primary key problem

Since SalesOrderDetail has a composite primary key, any foreign key that references it is also composite. For example, the association from SalesOrderDetail to SpecialOfferProduct (the specialOfferProduct) is mapped with a composite foreign key. NHibernate hides this detail from the C# code with the following association mapping from SalesOrderDetailEntity to SpecialOfferProductEntity:

<many-to-one name="FKSalesOrderDetailSpecialOfferProductSpecialOfferIDProductID" not-null="true" 
	access="field.pascalcase-underscore" outer-join="auto" insert="true" update="true"> 
	<column name="SpecialOfferID"/> 
	<column name="ProductID"/> 
</many-to-one>

Any collection owned by the SpecialOfferProductEntity class also has a composite foreign key - for example, the inverse association, SalesOrderDetailsSet.

<set name="SalesOrderDetailsSet" cascade="none" inverse="true" lazy="true" 
	access="field.pascalcase-underscore"> 
	<key> 
		<column name="SpecialOfferID"/> 
		<column name="ProductID"/> 
	</key> 
	<one-to-many class="AdventureWorks.Server.Data.AdventureWorks.Entity.SalesOrderDetailEntity, 
		AdventureWorks.Server.Data"/> 
</set>

1.4 Generated fields problem

Database column LineTotal is in SQL database script defined as:

[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))

So the created mapping is:

<property name="LineTotal" column="LineTotal" access="field.pascalcase-underscore" not-null="true" 
	type="System.Decimal" insert="false" update="false"/>

This property should be mapped with insert="false" update="false" to indicate that it is not to be included in SQL INSERTS or UPDATES by Nhibernate.

Note
After saving new SalesOrderDetailEntity, NHibernate is not aware of the value assigned to this column by specified trigger, because it occurred after the INSERT of the item row. If you need generated value in the application, you must explicitly tell NHibernate to reload the object with a SQL SELECT.

For example:

SalesOrderDetailEntity salesOrderDetail = new SalesOrderDetailEntity();
// open session
…
session.Save(salesOrderDetail);
// Force the insert to occur
session.Flush();
// Reload the object with a SELECT
session.Refresh(salesOrderDetail);
Console.WriteLine(salesOrderDetail.LineTotal);
…
//close session

With a Refresh method call "an in-memory instance in persistance state is refreshed with the current values present in the database."

A simpler technique with generated="insert" can also be used.Nhibernate then automatically executes a SELECT after insertion, to retrieve the updated state.

1.5 Huge amount of records on »many« side of a relation problem

This table also has another content related issue. It has a many-to-one relation to a table with 32.000 records. So when you include a combo box with no WHERE condition on such a many to one field, you will have a performance problem on your edit or report form. The problem appears not because of SQL execution, but because the form cannot paint itself in an appropriate response time. One of the quick solutions can be to use a “lookup” field instead of a combo box, which will be discussed in one of the future articles.

2 EmployeeDepartmentHistory table OR mapping issues

2.1 Table details (Figure 3)

Figure 3

Note
The similar state can be found in the table ProductCostHistory.

2.2 Problem of composed primary keys

As you can see here, there are four primary keys in a sample table. One of the primary keys is a simple DateTime column and the other three are many-to-one references. In this case you can elegantly create a separate composite unique identifier class QuadIdComponent. Unfortunately, many legacy schemas like this one use (natural) composite keys heavily and it may be difficult to change the legacy schema to use non-composite natural or surrogate keys. Therefore, NHibernate supports the use of natural keys. If natural key is a composite key, it is supported is via the <composite-id> mapping.

<composite-id name="Id" class="NConstruct.Server.Data.Core.Components.QuadIdComponent,
    NConstruct.Server.Data">
    <key-property name="Key1" column="ShiftID" type="System.Byte"/>
    <key-property name="Key2" column="DepartmentID" type="System.Int16"/>
    <key-property name="Key3" column="StartDate" type="System.DateTime"/>
    <key-property name="Key4" column="EmployeeID" type="System.Int32"/>
</composite-id>

What is important in QuadIdComponent implementation are its Equals and HashCode methods, since NHibernate relies on these methods for cache lookups (more about this can be read in one of the future articles).

In this case you can save your new instance of EmployeeDepartmentHistoryEntity with this code:

QuadIdComponent id = new QuadIdComponent(45, 34, DateTime.Now, 2); 
EmployeeDepartmentHistoryEntity history = new EmployeeDepartmentHistoryEntity();
// Assign a primary key value 
history.Id = id; 
history.EndDate = DateTime.Now; 
history.ModifiedDate = DateTime.Now; 
session.SaveOrUpdate();
session.Flush();

Note
How does NHibernate know that SaveOrUpdate requires an INSERT and not an UPDATE? It does not, so a trick is needed: NHibernate queries the EmployeeDepartmentHistory table for the given QuadIdComponent id, and if it is found, then NHibernate updates the row. If it is not found, then insertion of a new row is required and done. This is certainly not the best solution, because it triggers an additional hit on the database. Several strategies avoid the SELECT:

- Adding a <version> or a <timestamp> mapping.

http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html_single/#mapping-declaration-version
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html_single/#mapping-declaration-timestamp

- Implementing the NHibernate Interceptor.

Additionally, in this case you need to map a foreign key column that is also a part of a composite primary key with a regular element, and disable any NHibernate inserts or updates of this column with insert=”false” update=”false”, as follows:

<property name="StartDate" column="StartDate" access="field.pascalcase-underscore"
    not-null="true" type="System.DateTime" insert="false" update="false" />
<many-to-one name="Department" column="DepartmentID" access="field.pascalcase-underscore"
    not-null="true" outer-join="auto" insert="false" update="false" />
<many-to-one name="Employee" column="EmployeeID" access="field.pascalcase-underscore"
    not-null="true" outer-join="auto" insert="false" update="false" />
<many-to-one name="Shift" column="ShiftID" access="field.pascalcase-underscore"
    not-null="true" outer-join="auto" insert="false" update="false" />

So the edit form looks like the following:

Figure 4

NHibernate now ignores for example the Employee property when updating or inserting an EmployeeDepartmentHistory, but you can of course read it with history.Employee. The relationship between Employee and EmployeeDepartmentHistory is now managed through the Key4 property of the QuadIdComponent composite key class:

QuadIdComponent id = new QuadIdComponent(45, 34, DateTime.Now, employee.Id); 
EmployeeDepartmentHistory history = new EmployeeDepartmentHistory();
// Assign a primary key value 
history.Id = id; 
history.EndDate = DateTime.Now; 
history.ModifiedDate = DateTime.Now;
history.Employee = employee; 
history.Department = department; 
history.Shift = shift; 
history.StartDate = DateTime.Now;
session.SaveOrUpdate(); 
session.Flush();

Only the identifier value of the employee has any effect on the persistent state; the history.Employee = employee statement is done for consistency: Otherwise, you would have to refresh the object from the database to get the employee set after the flush.

The alternative approach would be a <key-many-to-one>. However, it is usually inconvenient to have an association in a composite identifier class. The <key-many-to-one> construct also has limitations in queries: You can not restrict a query result in HQL or Criteria across a <key-many-to-one> .

3 BillOfMaterials table OR mapping issues

3.1 Table details (Figure 5)

Figure 5

The table BillOfMaterials has the following foreign keys defined:.

ALTER TABLE [Production].[BillOfMaterials]
WITH CHECK ADD CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] 
FOREIGN KEY([ComponentID])
REFERENCES [Production].[Product] ([ProductID]) 

ALTER TABLE [Production].[BillOfMaterials]
WITH CHECK ADD CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] 
FOREIGN KEY([ProductAssemblyID]) 
REFERENCES [Production].[Product] ([ProductID])

3.2 Two foreign keys referencing the same table problem

The created mapping is as follows:

<many-to-one name="Component" access="field.pascalcase-underscore"
    not-null="true" outer-join="auto" insert="true" update="true">
    <column name="ComponentID"/>
</many-to-one>
    
<many-to-one name="ProductAssembly" access="field.pascalcase-underscore" not-null="false"
    outer-join="auto" insert="true" update="true">
	<column name="ProductAssemblyID"/>
</many-to-one>

4 WorkOrderRouting table OR mapping issues

4.1 Table details (Figure 6)

Figure 6

4.2 Non-created foreign key relationship problem

You have a ProductID primary key, which is also a foreign key. But a database architect did not create a foreign key constrain, so you had to map it like:

<property name="ProductID" column="ProductID" access="field.pascalcase-underscore"
    not-null="true" type="System.Int32" insert="false" update="false" />

In this case you would probably need to manually map this property as many-to-one association. The opposite is true for LocationID column which is correctly designed with a foreign key constraint and as a consequence mapped as:

<many-to-one name="Location" access="field.pascalcase-underscore"
    not-null="true" outer-join="auto" insert="true" update="true">
	<column name="LocationID"/>
</many-to-one>

4.3 OperationSequence index column and mapping problem

As you can see from the above tables there is an additional column named OperationSequence. We think that this could /must /may be mapped with <list> mapping. A mapping requires additional index column to the collection table. OperationSequence column can be considered as index column. The index column defines the position of an element in the collection. Thus, NHibernate is able to preserve the ordering of the collection elements. The user of NConstruct will be able to create it in a future (2.0) version of a builder.
So the mapping for WorkOrderEntity class could be as follows:

<list name="WorkOrderRoutings" table="WorkOrderRouting"
    lazy="true" access="field.pascalcase-underscore">
	<key column="WorkOrderID"/>
    	<index column="OperationSequence"/>
	<one-to-many class="AdventureWorks.Server.Data.AdventureWorks.Entity.WorkOrderRoutingEntity,
    		AdventureWorks.Server.Data"/>
</list>

5 Product table OR mapping issues

5.1 Table details (Figure 7)

Figure 7

5.2 Problems with fetching several “parallel” collections – Cartesian product problem

This table has no special mappings except for an unusually big number of collections. By default NConstruct builder creates a mapping with NHibernate lazy attribute set to true. One of the reasons for this design decision is remoting and an availability of NHibernate session issue.

Now, in the process of building an application with NConstruct builder, you have a lot of possibilities to define all kinds of different attributes on individual collection properties. You have to be very careful with some of them and you need to know exactly what are you doing. Let us expose one of the problems that you might cause:

Let us assume that you have made a decision to apply a global fetch=”join” (and lazy=”false”) setting to two of the collections of a ProductEntity class.

<set name="FKOrdersProductProductID" cascade="none" inverse="true"
    fetch="join" lazy="false" access="field.pascalcase-underscore">
	<key>
		<column name="ProductID"/>
	</key>
	<one-to-many class="AdventureWorks.Server.Data.AdventureWorks.Entity.OrdersEntity,
    		AdventureWorks.Server.Data"/>
</set>
    
<set name="FKProductReviewProductProductID" cascade="none" inverse="true" fetch="join"
    lazy="false" access="field.pascalcase-underscore">
	<key>
		<column name="ProductID"/>
	</key>
	<one-to-many class="AdventureWorks.Server.Data.AdventureWorks.Entity.ProductReviewEntity,
    		AdventureWorks.Server.Data"/>
</set>

When you try to edit a product record, NHibernate starts loading an entity. It executes an SQL SELECT that creates a PRODUCT of the two collections:

SELECT product.*,
fkOrders.*, 
fkProductReview.*
FROM [Production].[Product] product
left outer join [Sales].[Orders] fkOrders 
	on product.ProductID=fkOrders.ProductID 
left outer join [Production].[ProductReview] fkProductReview 
	on product.ProductID=fkProductReview.ProductID
WHERE product.ProductID=@p0

The result contains lots of redundant data. The size of the product depends on the size of the collections (number of rows) you are retrieving. Imagine you have 500 products in the database, and each product has 40 orders and 5 product reviews. Your result set can possibly contain 500*40*5=100.000 rows. The size of this result may well be several mega bytes. Considerable processing time and memory are required on the database server to create this result set. And all the data must be transferred across the network.

So when you try to fetch several parallel collections, the problem of Cartesian product appears. The Cartesian product may be defined as a SELECT statement that fetches too much data and is opposite of the n+1 selects problem.

You can only imagine what would happen if you would set that on all 8 collections.