A common problem when dealing with enterprise line of business applications is trying to minimize the access to the database, recently I was working a project that used NHibernate as its persistence layer and that was doing lots of queries to the database to just load some lookup table; the situation was similar to the following:

You have an entity that defines a list of choices a user can make, in out example we just call it ‘ListItem’, you then have another entity (we call that EntityUsingListItem) that just uses this ListItem class and can have a reference to a single ListItem or to a list of ListItem (this simulate the possibility for the user to select a single item or more items from a selection control); the user cannot add any element to the original ListItem collection; here are our test class:

public class EntityUsingListItem
{
	public EntityUsingListItem()
	{
		List = new List<ListItem>();
	}

	public int Id { get; set; }

	public string Name { get; set; }

	// a single reference
	public ListItem ListItem { get; set; }

	// a list of references
	public IList<ListItem> List { get; set; }
}

public class ListItem
{
	public int Id { get; set; }

	public string Name { get; set; }
	
	#region "Equality functions"

	public bool Equals(ListItem other)
	{
		if (ReferenceEquals(null, other)) return false;
		if (ReferenceEquals(this, other)) return true;
		return other.Id == Id;
	}

	public override bool Equals(object obj)
	{
		if (ReferenceEquals(null, obj)) return false;
		if (ReferenceEquals(this, obj)) return true;
		if (obj.GetType() != typeof (ListItem)) return false;
		return Equals((ListItem) obj);
	}

	public override int GetHashCode()
	{
		return Id;
	}

	public static bool operator ==(ListItem left, ListItem right)
	{
		return Equals(left, right);
	}

	public static bool operator !=(ListItem left, ListItem right)
	{
		return !Equals(left, right);
	}
	
	#endregion
}

The initial choice was to map both the classes to the database and have a bridge table for the many to many relationship. This basically means you have to create a specific table to hold the ListItem values and you also have to retrieve those values twice (once to populate your interface and every time your main entity was loaded from the database). This is obviously not optimal and can slow the things quite a bit. The optimal thing would have been to keep the ListItem table in memory since the beginning and have NHibernate persist only the reference to the ListItem objects in the form of an integer key; in my test example I’ve added a static collection to the ListItem object to hold the values, also notice the override of the equality function so that NHibernate can handle it in the proper way.

The problem is now how tell NHibernate to persist the object using an integer key and hydrate it performing a lookup against our in-memory table. Sometimes when you look at a problem the simplest solution can escape your grasp, so at start I’ve tried some complicated approaches (including realizing a custom persister for the class)...after some unsuccessful tries I’ve thought of using a custom IUserType implementation.

An IUserType is just a way to map a type to something else that can be understood by the database, perfect! Just what I was looking for! I throw away my 2 hours of work to implement my custom ListItemUserType solution in 10 minutes:

public class ListItemType : IUserType
{
	public SqlType[] SqlTypes
	{
		get
		{
			SqlType[] types = new SqlType[1];
			types[0] = new SqlType(DbType.Int32);
			return types;
		}
	}

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

	public new bool Equals(object x, object y)
	{
		return x != null && x.Equals(y);
	}

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

	public object NullSafeGet(IDataReader rs, string[] names, object owner)
	{
		//We get the string from the database using the NullSafeGet used to get strings 
		int id = Convert.ToInt32(NHibernateUtil.String.NullSafeGet(rs, names[0]));

		return ListItem.GetList().Where(i => i.Id == id).Single();
	}

	public void NullSafeSet(IDbCommand cmd, object value, int index)
	{
		//Set the value using the NullSafeSet implementation for string from NHibernateUtil
		if (value == null)
		{
			NHibernateUtil.String.NullSafeSet(cmd, null, index);
			return;
		}
		NHibernateUtil.String.NullSafeSet(cmd, ((ListItem)value).Id.ToString(), index);
	}

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

	public bool IsMutable
	{
		get { return false; }
	}

	public object Replace(object original, object target, object owner)
	{
		//As our object is immutable we can just return the original
		return original;
	}

	public object Assemble(object cached, object owner)
	{
		//Used for casching, as our object is immutable we can just return it as is
		return cached;
	}

	public object Disassemble(object value)
	{
		//Used for casching, as our object is immutable we can just return it as is
		return value;
	}
}

The key points are:

• I tell NHibernate how to represent this type at the database level (as a single integer column), lines 7-9.
• In the NullSafeGet() implementation I read the integer key of the object and perform the lookup using Linq, lines 30-33.
• In the NullSafeSet() I just tell NHibernate to persist only the id of the object,lines 38-44.

The mapping is extremely simple:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
				   default-lazy="false" 
				   default-cascade="all-delete-orphan"
				   assembly="YYY"
				   namespace="XXX">
	<class name="EntityUsingListItem" table="EntityUsingListItem"  >
		<id name="Id" type="Int32" >
			<generator class="native" />
		</id>
		<property name="Name" type="string" />
		
		<property name="ListItem" type="XXX.ListItemType, YYY" />

		<bag name="List" table="EntityListBridge" >
			<key not-null="true" />
			<element column="listid" type="XXX.ListItemType, YYY" not-null="true" />
		</bag>
	</class>
</hibernate-mapping>

In this way we only persist the integer Id of the ListItem when the object is saved (the user type do it for me) and when it is loaded I get the full instance of the ListItem class in each referencing properties (again, the user type does the lookup for me); the result are less data that go back and forth to the database each time you load your main entity and the whole application will be faster, especially if you have lots of lookup values.

If all your lookup classes are derived from the same base class it’s easy to realize a generic version of this custom user type and use it to load and save all your lookup values reusing the code.

If at a later time, you need to persist this table to the database because the users can now add and remove items from the ListItem values collection, you can easily change the mapping again without loosing any of your data already stored in the database.

Related Content

• Breaking news: the First European NHibernate Day had been officially announced (26/08/2015)
• NHibernate - First impressions on ConfORM (26/08/2015)
• NHibernate - Customize the Linq provider to call your user defined SQL functions (10/01/2010)
• The NHibernate Day is coming...this Saturday! (10/06/2010)
• #nhday - after event thoughts (10/11/2010)
• Hands on ConfORM: mixed mapping techniques (26/08/2015)
• More related document (20)