ORM:1, Hand Coding:0

By definition I am lazy, I love using tools to reduce my workload. Object Relational Mapping (ORM) is one of those tools that I can not live without.

Several projects ago we decided that it would be better if we generate at least 85% of persistence layer automatically, but still would have control of how and when do we use ORM. NHibernate was our choice of the ORM (at time it was beta 0.8).

So why does ORM rule? Well, I would like to share a great story on how we were able to leverage NHibernate. On one of my current projects we are using NHibernate (1.03 beta) to generate the persistence layer for the use with Oracle 10G R2.
As we were reviewing and finalizing the requirements for purchasing production hardware and licenses, the client’s newly hired CIO, asked us: “why do we need to have both Oracle and SQL Server?” Of course we answered that: “we are using Oracle because it is your preferred choice of database to persist application data. SQL Server is a requirement for the BizTalk 2006, which we are using as a middleware server.”
The next question was: “What will it take to switch to SQL Server completely?” I should mention this conversation took place half way through our development effort. Our response was: “since we are using ORM to abstract the database from the application, it should only take 54 hours.”
Here is a list of tasks we had to do:
1. SQL Server Setup (Total Effort: 4 hours)
2. Database Conversion (Total Effort: 24 hours)
a) re-create 135+ tables from the Database Definition Language (DDL) scripts;
b) make unit conversion updates like: Change VARCHAR2 to VARCHAR, CLOB to VARCHAR(MAX), NUMBER to BIGINT, NUMBER (1) to BIT/BOOLEAN
c) update defaults, identities
d) update naming conventions
e) update DDL Script that generates triggers
f) update 5 stored procedures and functions
3. Code Updates (Total Effort: 10 hours)
a) Update the NHibernate XML Mapping files (82+)
b) Update the method that calls stored procedures
4. Regression Testing (Total Effort: 16 hours)

As soon as we got the green light from the CIO to go ahead and make the conversion, our DBA and Lead Developer spent one week-end making the conversion. It surprised me that our actual implementation time was 55 hours, and most of that was spent in data conversion (32 hours).

So on the following Monday, one of developers had a shocking experience. In the morning he was working on one of his features that required saving. Typically, he would open Toad to view the table he was saving to, in order to verify the results. According to him, he was getting pretty mad, because a simple save feature started to look like a nightmare. He would type the values on the form, click save, get no errors, go look for the results in Toad and see no changes in the table, sol he tries to reload the form/restart application/visual studio/computer and see that typed values are loading back, while still not showing in the database. How can you troubleshoot a problem like that?
Luckily, we have "Daily Stand Up Meetings," at which everyone gives updates on items they are currently working and have completed. So as the news of the database switch were announced, you could clearly hear swearing from the developer. Mel, feel free to add to the story.


In the end:
- listening to the boss on why we should be using ORM is 0.0001 man hours,
- choosing an ORM is 40 man hours,
- crash learning NHibernate is 80 man hours,
- getting comfortable with NHibernate is 160 man hours,
- switching database during the development without developers noticing is PRICELESS!

NHibernate repository variation on the Criteria

After reading Dave Donaldson post on NHibernate repository, I was pleasantly surprised on how similar our implementations are. By 'our' I mean Kevin Spargue and I; we are using NHibernate on one of our projects. This is my third NHibernate project and this time around we have enough time to sit down and make corrections to hard learned lessons of using NHibernate. One of those days I will get around publishing our version of the NHibernate repository, but for right now I would like to add our variation on dealing with criterias.

Originally we started with the similar implementation as in Dave's repository:
public static Collection FindByProperty(string property, object value)
with an overload
public static Collection FindByProperty(string[] property, object[] value)

but then we have decided against it, and end up with creating a class wrapper that have included property value, name and operator. This implementation helped us to reduce the number of methods we need to maintain and have provided us more flexibility when it comes to & finding/filtering data.

Repository methods
public static IList Find(QueryValueCondition[] queryValueConditions, QueryOrCondition[] queryOrConditions)
where T : IPersist
{
if (queryValueConditions == null) queryValueConditions = new QueryValueCondition[0];
if (queryOrConditions == null) queryOrConditions = new QueryOrCondition[0];
IList results = new List();
Type type = typeof(T);
IList list = Find(type, queryValueConditions, queryOrConditions);
foreach (T item in list)
results.Add(item);
return results;
}

public static IList Find(Type type, QueryValueCondition[] queryValueConditions, QueryOrCondition[] queryOrConditions)
{
IList results = Accessor.LoadList(type, queryValueConditions, queryOrConditions);
return results;
}

Accessor implementation (Helper class consists of a few static methods that help us final query assembly.)

public static IList LoadList(Type type, QueryValueCondition[] queryValueConditions, QueryOrCondition[] queryOrCondition)
{
IList results = null;
ICriteria criteria = NHibernateSession.CreateCriteria(type);
foreach (QueryValueCondition valueCondition in queryValueConditions)
criteria.Add(Helper.GetCritria(valueCondition));
foreach (QueryOrCondition orCondition in queryOrCondition)
criteria.Add(Helper.GetOrExpression(orCondition));
results = criteria.List();
return results;
}

I will not dive into the details of the methods, but I do want to talk about: QueryValueCondition and QueryOrCondition.

QueryValueCondition class consists of properties wrapped around fields:
string _propertyName = string.Empty;
object _propertyValue = null;
Operators _operators = Operators.Equals;

Operators is a simple enumeration
public enum Operators
{
Equals,
GreaterThan,
GreaterThanEqualTo,
LessThan,
LessThanEqualTo,
Like,
IsNull,
In,
IsNotNull
}

This class allowed us to group a set of data into a logical unit, that contains everything we need to be able to retrieve data on a passed property value.


QueryOrCondition class contains wrap around properties for following fields:
QueryValueCondition _leftCondition = null;
QueryValueCondition _rightCondition = null;

Just an additional class that allow to group multiple properties into OR condition. So now we can use one Find method to get data by one or more properties and we have a power of matching it by various conditions.