Performance differences between LINQ To SQL and NHibernate

In my current project one of the actions I’ve taken is to have the project and team move away from Linq To Sql to NHibernate. There was a multitude of issues that was the basis for this move, some of the main reasons are outlined in my post:“Top 10 reasons to think twice about using Linq To Sql in your project” but there was also others, like the inability to tweak Linq To Sql to perform in different scenarios which often lead to stored procedures. NHibernate have a multitude of buttons to push and can tweak almost every aspect of the data access which gives us more options.

This post is not about the lack of optimizing options in LTS, nor the options in NHibernate. It’s just to illustrate a simple truth; NHibernate is more mature and have had time to optimize the core functionality like object creation (materialization). Compare these two images:



Linq To Sql Materialization 703ms -  DB access 7 ms



NHIbernate materialization 159 – DB access 7 ms

Unfortunately I can’t show the real model that we loaded but it was an aggregate with a couple of lists which where 3 levels deep.

Another thing to note here: Linq To Sql needed a stored procedure to load this (due to the fact that load spans work really really bad in depths deeper then 2 levels, read more here: LINQ To SQL: Support for Eager Loading, Really? ) NHibernate usages a couple of batched select queries that was built using NHibernate Criteria and the Future feature.

Look at the database execution times, both are 7ms. So can we please kill the “stored procedures are faster then ORM generated queries”-debate now? Each of these scenarios is run on the same database with the same indexes and both had a database that where equally warmed up before the runs.

Tracking a property value changes over time: Temporal property using NHibernate

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”.

image 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<DateTime, Quantity> 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)

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;

imagefigure 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> list type and a definition of an index. In our case, the index is our DateTime. Listing 2 shows the mapping for this scenario:

<map name="quantityTrail" access="field" inverse="false" cascade="all-delete-orphan">
  <key column="ProductInventoryId" />
  <index column="EffectiveDate" type="DateTime" />
  <one-to-many class="Quantity" />
<class name="Quantity" table="ProductInventoryQuantites">
  <id name="Id">
    <generator class="identity" />
  <property name="_quantity" column="Quantity" access="field" />    

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; }
        _quantity = value;

listing 4, changes to the quantity property for optimization

<property name="Quantity" 
          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]

Creating a dynamic state machine with C# and NHibernate, Part 2: Adding business rules.

This the second part of a series started in an earlier post; Creating a dynamic state machine with C# and NHibernate 

In my first post I showed you how to create a state machine, attach it to an entity and then save it using NHibernate. In this post we’ll extend the state machine with the capability of adding business rules that must be fulfilled to allow transitions. These rules will be dynamically added to each state and persisted to the database using NHibernate.


Extend the model with business rules using strategy pattern

The first step will be to use an implementation of the Strategy Pattern to ensure that our business rules engine is open for extension (thus following the open-closed principle). First we’ll define an interface to use for our business rules, listing 1 shows the definition;


public interface IRule
    bool IsMetBy(Template entity, State state);

listing 1, the IRule interface

The IsMetBy method accepts the entity that the state is attached to which we’ll be using as data for our rules later. Next we add a list of business rules to the state class;


public class State

    private IList<IRule> _transitionRules = new List<IRule>();
    public virtual IList<IRule> TransitionRules
        get { return _transitionRules; }
        set { _transitionRules = value; }

    public virtual bool HasAllTransitionRulesMetBy(Template entity)
        var transitionIsAllowed = true;
        foreach (var rule in TransitionRules)
            transitionIsAllowed &= rule.IsMetBy(entity, this);

        return transitionIsAllowed;

listing 2, list of rules on the state class

With this addition every state now holds a list of rules that need to be fulfilled before the state accept being changed into it. We’ve added a method that runs through all the business rules for the state and validate that all of them are met. This a call to HasAllTransitionRulesMetBy is added to the templates ChangeStateTo method;

public void ChangeStateTo(State newState)
    if (State.CanBeChangedTo(newState) && newState.HasAllTransitionRulesMetBy(this))
        State = newState;
        throw new InvalidStateTransitionException();

listing 3, changes to the ChangeState method

At this point changing state will run through the list of allowed transitions, functionality we added in the first part, and the list of rules to make sure the transition is allowed. We honor the open – closed principle by allowing rules to be added in a simple fashion, thus not relying on a lot of refactoring when rules change or get’s added.

Implementing a rule

To make this state machine meaningful we need to start creating business rules. For convenience I’ve added a base class Rule that implements some properties needed later but in essence it’s the same as our interface. First rule will ensure that an entity from the template has a ScheduledHours property with a minimum of time. Listing 4 shows our first rule,


public class IsScheduledForAtLeast : Rule
    public virtual int ScheduledHours { get; set; }

    protected IsScheduledForAtLeast() {}

    public IsScheduledForAtLeast(int scheduledHours)
        this.ScheduledHours = scheduledHours;

    public override bool IsMetBy(Template entity, State state)
       if ( entity.ScheduledHours >= ScheduledHours && state == "Closed" )
           return true;

        return false;

listing 4, an example rule

In a typical scenario these rules might be a bit more complex and in part three of this series we’ll look into rules that need more then just the entity state to get it’s work done. Figure 1 displays the model we’ve built so far;


Figure 1, our model so far

A test to validate this looks something like listing 5;

public static class States
    public static State ClosedState = new State("Closed")
    { TransitionRules = new List<IRule>
    { new MinimumAttendanceRule(8), new IsScheduledForAtLeast(4)} };

    public static State OnGoingState = new State("OnGoing");

    public static State OpenState = new State("Open")
    { AllowedTransitions = new List<State> { "Paused", ClosedState } };

public void It_will_not_allow_state_transition_from_closed_to_open()
    var entity = new Entity(States.ClosedState);

        () => entity.ChangeStateTo(States.OpenState));


Using NHibernate to persist a template with state and business rules.

So far we can build a state machine that is setup with transition rules and business rules for each state, but only in memory. For this to be meaningful we actually need to persist it as well. For our scenario we want to persist the template, it’s current state, all allowed state transitions and the rules added to each transition.

An example setup that we need to persist looks like listing 6;

public void Save_a_state_with_transition_rules_added()
    var savedState = new State("Open")
                             TransitionRules =
                             new List<IRule> {
                                new MinimumAttendanceRule(8),
                                new IsScheduledForAtLeast(5)}

    var fetchedState = validationRepository.Get(savedState.Id);

    Assert.That(fetchedState.TransitionRules.Count, Is.EqualTo(2));

    rule => rule is MinimumAttendanceRule), Is.Not.Null);

    rule => rule is IsScheduledForAtLeast), Is.Not.Null);

So how do you save something this dynamic to the database? There is no way of telling what rules will be added and certainly not a table structure that will fit. Can we do it? Yes we can. Using inheritance mapping in NHibernate this is very possible. For our scenario we’re using the inheritance type “Discriminator column” and a many-to-many relationship between state and rule. The database tables for this will look like figure 2;

imagefigure 2, Table structure 

As figure 2 shows it is now possible to store every state with it’s transitions, their rules and any configured value needed (we serialize all values into one column at the moment). We need to update our NHibernate mapping to include the list of rules and all implemented rule types. Listing 7 shows the mapping files for this scenario;


<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="State" table="States">    


    <bag name="TransitionRules" cascade="all" table="TransitionRules">
      <key column="StateId" />
      <many-to-many column="RuleId"  class="Rule" />

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="Rule" table="Rules" abstract="true">
    <id name="Id" type="int">
      <generator class="native" />
    <discriminator column="Name" />

    <subclass discriminator-value="IsScheduledForAtLeast"
      <property name="ScheduledHours" column="Value" />

listing 7, NHibernate mapping




With the usage of interfaces and the open-closed principle, we get a flexible way to add rules to our state machine. These rules can easily be added in a user interface and several templates with different sets of states and business rules. Using some powerful mapping techniques in NHibernate this is persisted easily as well.

This was part 2 of a three part series. In the last part we’ll be using dependency injection in our rules to enable more advanced scenarios. I’ll also provide you with a complete end-to-end sample solution.

Managing Parent/Child relationships with NHibernate (Inverse management)

When working with parent/child relationships in object models it is important to know what kind of Inverse Management your ORM technology have. Inverse management means handling all the relationships and keys shared between the parent and the child. This post will help you understand how NHibernate manages these relationships and what options you have.

Standard parent / child with inverted properties

The standard parent / child object model usually looks something like the below picture;

image figure 1, Standard parent / child

In figure 1 you see that the comment entity has an inverse property back to product.

NHibernate requires you to manually set the product property on the comment to the correct object, it has no automatic inverse management of properties. This is usually done by adding an “AddComment” method on the product that encapsulate the logic needed to get the relationship right.

The below represents how the foreign key constraint in the database looks. Figure 2 shows the standard parent / child;


figure 2, parent child database model

In this case the inverted property ensures that the comment object itself will “contain” a copy of the product Id to insert into the database. You tell NHibernate about this relationship and how to handle the keys by setting up the mapping like listing 1;

<class name="Product" table="Products">
  <bag name="Comments" inverse="true" cascade="all">
    <key column="ProductId" />
    <one-to-many class="Comment"/>

<class name="Comment" table="Comments">
  <many-to-one name="Product" column="productId" />

listing 1, standard parent / child mapping

Using the above xml NHibernate will have enough to figure out that the product id should be persisted into the comments table together with the comment itself.

The bag mapping tells the product that there is an inverse property on the comment and instructs NHibernate to let the comment handle the relationship on it’s own.

Variation 1, no inverse property on the child

A common approach in object modeling is to use aggregate roots and just let the relationship flow from the parent to the child, not an inverse back. This makes sense when you think about the object behaviors; comment will never stand on its own, it will always be accessed through the product.

Figure 3 illustrates how the such a model looks like;

imagefigure 3, Aggregate model

This approach leaves NHibernate a bit dry. In this variation; comment can’t stand on its own and will not be able to deliver the product id to the database. It will instead rely on the comments list from the product to provide that. NHibernate needs to be told that this is your intention the bag declaration has to be changed into;

<bag name="Comments" inverse="false" cascade="all">

NHibernate now knows that the comment entity doesn’t have a parent property that points back.

There is a caveat with this though, NHibernate waits a bit to insert the identity of the product into the comment. Figure 4 shows the statements NHibernate sends to the database;

image figure 4, Statements sent to the database

As you can see, the product id is sent in a separate statement after the rows have been inserted. This means that the product Id column in the comments table has to be nullable. As long as this save will be in a transaction and the amount for rows are small, this will be a viable solution. Just be aware of the mechanics NHibernate uses.

Variation 2, the hybrid solution

If you don’t want the inverse property and can’t set the foreign key to nullable the two above solutions won’t help you. For this variation you need to put a hybrid solution together.

This is a similar to the standard parent / child, but instead of the full entity we will only use a protected field on the comment. The field you want to add would look something like the following;

protected int _MAP_productId;

which then would be mapped like a regular property, not an object reference;

<property name="_MAP_productId" access="field" />

Note: It’s usually a very good idea to name the field with an awkward name like the one above, this ensures that developers after you will think twice before using it for any other purpose then mapping. This is also a place where I would consider adding a code comment.

To set the field you could either create a constructor or expose an internal property that the product can use. Don’t try to write to the field from the outside directly, NHibernate has issues with internal fields and making it public will just be ugly.

The drawback with this approach is that NHibernate won’t be able to automatically set the identity on the comment. This means that you have one of two options for getting that product id:

  1. Don’t use auto-generated Id’s, make sure you assign one to the product before adding any comments.
  2. Save the product first, before adding any comments to it. This way the Id will be set in time.

I’m sure there is an extension point somewhere in NHibernate that would allow for the above variation to be automatically handled. I will get back to you when and if I find it.


The object model and relational model are different schemas and as such compromises have to happen. NHibernate makes a very good job in hiding those compromises in most cases, but when it comes to inverse management you the developer need to take a stand on what compromise is the right one for your solution. Now you know your options, choose wisely.


Nhibernate project website:

NHibernate documentation about parent / child:

NHProf application by Ayende that was used to inspect the queries sent:

Top 10 reasons to think twice about using Linq To Sql in your project

I love ORM technology. I use a lot of it when building applications. I never did get completely in love with Linq To Sql though.  I’ve been using it a lot to teach ORM fundamentals just because the learning curve is really low. It just hasn’t appealed to me for production systems.

In my current project I’ve had reason to revisit the reasons why Linq to Sql failed to appeal me and this is the top 10 list.

10) A lot of the generated T-SQL queries are unnecessary complex. Take a look at the query generated by a simple  “firstName like ‘%a%’” kind of query:

SELECT [t0].[id], [t0].[firstname], [t0].[lastname], [t0].[streetaddress],
[t0].[city], [t0].[zipcode], [t0].[email]
FROM [dbo].[Director] AS [t0]
        WHEN (DATALENGTH(@p0) / 2) = 0 THEN 0
        ELSE CHARINDEX(@p0, [t0].[firstname]) - 1
     END)) > @p1

9) Associations between entities in most cases means keys has to be expressed twice in the model (once in the child entity and once in the parent entity). This leads to awkward entity design.

8) There is no batch fetch or batch insert/update for when you want to send a lot of rows to the database in one go. This seriously cripples Linq To Sql’s ability to handle anything but smaller object graphs.

7) Linq To Sql lacks the ability to do many-to-many relationships.

6) Inheritance is limited to one of the least useful scenarios; discriminator columns.

5) You can’t break up a big table into a nice object graph. There is no support for components so you can’t have a table that look like this:


And have an entity that looks like this:


4) You can’t map your own value objects. So instead of a reusable class like this:

 public class Director
    { ...
       public Email Email { get; set; }
    ... }    

You need to do this:

    public partial class Director
    {   ...
        partial void OnEmailChanging(string value)
        }        ...

3) There is insufficient support for loading N+1 relationships. Linq To Sql only supports joins of 1+1 level graphs, for N+1 it starts throwing out selects. I have blogged about this before: Linq To Sql: Support for Eager Loading, Really?

2) It’s table driven and enforces the database design upon your entity model, leaving you no room for clever modeling in code that aren’t available in relational table structure models.

1) Microsoft has announced that Entity Framework is their recommended data access strategy. 

So to sum up, Linq To Sql just don’t meet my needs for a competent ORM that allows me to model my application as I wish. It doesn’t give me the high performance from the data access I want either.

If the limitations I see don’t worry you are aren’t applicable in your projects, by all means use it.