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:

image

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.

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://theguidestar.com iwayneo

    If this was an SP you could query the DB to get the columns output from the SP then use DynamicLinq class creation to generate a class at runtime to pass to the SetResultTransformer(Transformers.AliasToBean(typeof(DynamicClass)))
    And then have fully dynammic mappings!!

    cool!

  • Shehab

    I have to say that I have been searching for days till I found your blog.

  • dario-g

    Really? Wow. I’m surprised that you didn’t know AliasToBean earlier.

    How you do query for lists? Pull whole objects?

  • Rafael Paschoal

    I Just loved it! I have been trying to find a solution for my report engine for days, and this seens to be the best one for my application! I’m using MVC 3 Framework along with Fluent Nhibernate and Crystal Reports. Awesome!