Lookup table for constants with NHibernate

Databases are pretty commonly used to keep lists of constant values, so called dictionaries or lookup tables. Applications are just loading all the values at startup and keeping them in memory for the whole run for performance reasons.

This doesn't sound like a real use case for such a powerful tool like the database, designed to handle relations, transactions, aggregations and a lot of other complicated stuff. But it's often most convenient to create just one more table for our dictionary - we know how to manage the database and it's already there. Creating another integration point for our application just to keep a list of countries probably makes no sense.

How should we handle dictionaries in NHibernate? Well, in fact, we shouldn't rely on NHibernate too much in this case. NHibernate is designed to manipulate entities and constant values are not entities. These are just some plain objects, that accidentally came from the database.

Let's say we want to have list of countries in our database to show in dropdown within user address form. When fetching Country objects as entities, NHibernate does a lot of unnecessary stuff. Each Country object is tracked for changes that will never occur. Entities are also attached to the ISession that fetched it. We don't want to keep this session forever for sure, but this means that our entities will span across multiple sessions. That's bad, too - entities are supposed to live in scope of single Unit of Work.

For fetching the constant data from the database, we should choose any method that doesn't produce objects as persistent entities. We can either use plain ADO.NET connection and construct the objects ourselves, or use CreateSqlQuery with AliasToBeanTransformer, as I showed in the previous post. In both methods we fall back to plain SQL, but it is very straightforward and portable, and the timings are very good.

Suppose we're using Country objects as entities properties in some parts of our application. In this case we shouldn't use the same objects for querying and data manipulation than for filling in the dropdown. Objects in dictionaries are constants, for lookup purposes only. Country object used as a part of entity are managed by NHibernate and it should not be the same instance as Country constant in dictionary, even if they are both instances of Country class that represents the same country. In fact, it'll be better to have two separate classes for countries, so that objects are explicit about their roles and they don't abuse Single Responsibility Principle.

In some cases - i.e. when we're listing users for given country - it may be convenient and logically correct to use the constant values for querying. In scenarios like that, our entities need to have a relation to constant value. This can be done using custom IUserType that translates id (or any other key our Country has) at database level to the Country object from our lookup.

public class CountryType : IUserType
{
    public SqlType[] SqlTypes
    {
        get
        {
            return new[] { NHibernateUtil.Int32.SqlType };
        }
    }

    public Type ReturnedType
    {
        get 
        { 
            return typeof(Country); 
        }
    }

    public new bool Equals(object x, object y)
    {
        return Object.Equals(x, y);
    }

    public int GetHashCode(object x)
    {
        return x.GetHashCode();
    }

    public object NullSafeGet(IDataReader rs, string[] names, object owner)
    {
        var id = NHibernateUtil.Int32.NullSafeGet(rs, names[0]);

        if (id == null)
            return null;

        // get constant object from our lookup table
        return CountryLookup.ById((int) id);
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        if (value == null)
            NHibernateUtil.Int32.NullSafeSet(cmd, null, index);
        else
        {
            // persist only key of our constant object
            NHibernateUtil.Int32.NullSafeSet(cmd, ((Country)value).Id, index);
        }
    }

    public object DeepCopy(object value)
    {
        if (value == null)
            return null;
        var country = (Country)value;
        return new Country() { Id = country.Id, Name = country.Name };
    }

    public bool IsMutable
    {
        get { return false; }
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }
   
    public object Assemble(object cached, object owner)
    {
        return cached;
    }

    public object Disassemble(object value)
    {
        return value;
    }
}

This way, the fact that Country is not really an entity is transparent to our code, we can query by Country, have Countries in relations or whatever we need, without breaking the rule that constants are not entities.