Lowendahl's Shout

A common problem that often needs to be solved is to answer a question like “how did my inventory look 1 month ago?”. Keeping this kind of history, and query over it, can be tedious and is something that need to be thought about for a bit. In this post I will show you how you can track a single property in time using the Temporal property pattern written down by Martin Fowler and NHibernate to persist the change over time.

Implementing the temporal property pattern in C#

This example is derived from a solution in the current project I am in. The idea is that we have a bunch of products in a warehouse. The number of products in stock varies, of course, and we would like to ensure that the application always can answer the question “How many of product x did I have at date y”.

 figure 1, the inventory class

Figure 1 show the basics of the ProductInventory class. I’ll show you how to extend the Quantity property as a Temporal property.

To ensure that we keep track of all the values that Quantity ever had, we need to save those values in a table and attach some date of validity to it. Martin’s original pattern suggests that every value gets a date range with a Valid From and Valid To attribute. After discussing this a bit with Philip Nelson, who works with temporal data daily, I’ve come to the conclusion that a single column that states “EffectiveDate” is usually good enough and certainly good enough for this scenario.

First off, let’s add a mechanism that allows us to track these values. For this we will be using a dictionary:

protected IDictionary quantityTrail = ... 

The dictionary will use DateTime as key and a Quantity entity (with implicit operator for int and all) to hold the quantity values. To work with this list we’ll add a couple of methods and make some changes to the Quantity property;

public virtual Quantity Quantity
{
    get { return QuantityAt(DateTime.Now); }
    set { AddTrail(value); }
}

private void AddTrail(Quantity value)
{
    quantityTrail.Add(DateTime.Now, value);
}

public virtual Quantity QuantityAt(DateTime date)
{
    return (from item in quantityTrail
            where item.Key <= date
            orderby item.Key descending
            select item.Value)
        .FirstOrDefault();
}

listing 1, code to add temporal functionality

As you can see in listing 1 we’ve changed the setter to save the value to the dictionary instead of a field. We’ve also changed the getter to ask for the quantity in the Now time frame.

In listing 1 you also see the QuantityAt method that looks in the dictionary and picks out the entry that is older or equal to the date provided. Congratulations you have now implemented a temporal property with C#. Figure 2 shows the end result;

figure 2, end result 

Storing temporal properties using NHibernate

NHibernate comes with built in support to easily store a dictionary in a table. This is done using the

<map name="quantityTrail" access="field" inverse="false" cascade="all-delete-orphan">
  <key column="ProductInventoryId" />
  <index column="EffectiveDate" type="DateTime" />
  <one-to-many class="Quantity" />
map>   

...

<class name="Quantity" table="ProductInventoryQuantites">
  <id name="Id">
    <generator class="identity" />
  id>
  <property name="_quantity" column="Quantity" access="field" />    
class>

Listing 2, mapping to save a dictionary in the database

With these two pieces of the puzzle we’ll now start to track changes to the Quantity in our database. The entity will always deliver the latest value for the property and you are free to load-optimize the history as you find suitable (lazy / eager).

Optimizing for simpler querying

For faster query I’ve tweaked the basic solution a little bit. Instead of saving all the values in the trail I’ve added the current value to the entity table and are just passing new values into the dictionary. Listing 3 and 4 shows the optimization changes:

private Quantity _quantity;
public virtual Quantity Quantity
{
    get { return _quantity; }
    set
    {
        _quantity = value;
        AddTrail();
    }
}

listing 4, changes to the quantity property for optimization

<property name="Quantity" 
          column="CurrentQuantity" 
          type="QuantityTypeMap, Core" />

Listing 5, added property mapping for optimization

This little “optimization” will allow for loading the entity from one table in a “normal” case and then lazy load the list of history values to call QuantityAt() when asking for history.

Conclusions and downloadable code

So with a little bit of OO and great help from NHibernate it’s fairly simple to track historical data for a property. I am not sure how other ORM’s would solve this, if they can. Do you?

Code example: TemporalPattern.Zip [12k]