Ad-hoc mapping with NHibernate

In my recent adventures with massive bulk processing, there are some times when I want to pull bulk loaded tables from SQL, but don’t want to go through all the trouble of building a mapping in NHibernate.  For example, one recent project had an intermediate processing table of something like:

This table is used in a bulk copy scenario, so it’s very string-based to ease the burden of bulk loading.  Later, we’ll transactionally process this table to update our actual customer table.  In the meantime, we want to use this data in a .NET application.  We have a few options:

• Load into a DataSet
• Stream from an IDataReader
• Map using NHibernate
• Ad-hoc map using NHibernate

Many times, I like to go with the last option.  DataSets and data readers can be a pain to deal with, as most of the code I write has nothing to do with dealing with the data, but just getting it out in a sane format.

NHibernate supports transformers, which are used to transform the results of the query into something useful.  To make things easy, I’ll create a simple representation of this table:

public class BulkLoadCustomer
{
    public string CustomerId { get; set; }
    public string RegisteredDate { get; set; }
}

I’ll create some generic query:

var sql = "SELECT CustomerId, RegisteredDate FROM BulkLoad.Customer";

var sqlQuery = _unitOfWork.CurrentSession.CreateSQLQuery(sql);

I just have the NHibernate ISession object exposed through a Unit of Work pattern.  With the ISqlQuery object that gets created with the CreateSQLQuery() method, I can then specify that I want the results projected into my custom DTO:

var results = sqlQuery
    .SetResultTransformer(Transformers.AliasToBean(typeof(BulkLoadCustomer)))
    .List<BulkLoadCustomer>();

The AliasToBean method is a factory method on the static Transformers class.  I tell NHibernate to build a transformer to my DTO type, and finally use the List() method to execute the results.  I don’t have to specify any additional mapping file, and NHibernate never needs to know about that BulkLoadCustomer type until I build up the query.

The name “AliasToBean” is a relic of the Java Hibernate roots, which is why it didn’t jump out at me at first.  But it’s a great tool to use when you want to just map any table into a DTO, as long as the DTO matches up well to the underlying query results.