In normal query scenarios we can rely on NHibernate's mechanisms to convert the query results into entities and objects that we can use. Sometimes, however, the queries may not map to classes we have defined in the mappings or maybe we want to customize what is returned. NHibernate provides many extension points and one of those is a result transformer, which can be injected into the flow of a query. It transforms the results of a query into the results that we need.
In the following recipe, we will try out two of the most commonly used built-in transformers.
- Complete the steps in the Getting Started section at the beginning of this chapter.
- Add a new folder named
ResultTransformersto the project. - Add a new
classnamedRecipeto the folder:using System; using NH4CookbookHelpers; using NH4CookbookHelpers.Queries.Model; using NHibernate; using NHibernate.Transform; namespace QueryRecipes.ResultTransformers { public class Recipe : QueryRecipe { protected override void Run(ISession session) { var movieQuery = session.QueryOver<Movie>() .Inner.JoinQueryOver(x => x.Actors); Console.WriteLine( "Result count without transformer:{0}", movieQuery.List<Movie>().Count); movieQuery = movieQuery. TransformUsing(Transformers.DistinctRootEntity); Console.WriteLine( "Result count with transformer:{0}", movieQuery.List<Movie>().Count); var bookResults = session.CreateSQLQuery(@" select b.Name, b.Author,p.Name as PublisherName from Product b left join Publisher p ON b.PublisherId=p.Id where b.ProductType = 'Book'") .SetResultTransformer(Transformers.AliasToBean<BookInfo>()) .List<BookInfo>(); Console.WriteLine("BookInfo objects:"); foreach (var result in bookResults) { Console.WriteLine("{0}, by {1}, published by {2}", result.Name, result.Author, result.PublisherName); } } } } - Run the application and start the
ResultTransformersrecipe.
Result transformers don't act on the raw query results from the database. They come in the second phase, after the query results have been hydrated into objects.
In the first query of the recipe, the DistinctRootEntityResultTransformer (accessed by the shorthand Transformers.DistinctRootEntity) is fed a list of Movies, which due to the joined Actors property may contain several duplicates. It can remove all the redundant rows by feeding the entities' IDs through a HashSet, which doesn't allow duplicates.
The second query is a native SQL query, but could just as well have been an HQL query or a Criteria or QueryOver query with a special projection. We want to project just a few columns in a query and map those columns to properties of a class that we haven't even mapped. The class is completely unknown to NHibernate.
For that purpose we need to use a transformer that maps the results into BookInfo instances (which the transformer creates using the default constructor), based on property names and result column names. The AliasToBeanResultTransformer does just that. Note that the result column (alias) names must exactly match the property names and that all the returned columns must have a corresponding property, otherwise an exception will be thrown.
The AliasToBeanResultTransformer is very useful for custom queries, such as aggregates for reports, when very specific results should be mapped into convenient Data Transfer Objects.
NHibernate has a range of result transformers that can be used in specific scenarios. Here's a short walk-through:
As mentioned previously, this transformer takes a list of results and reduces it so that all duplicates of the root entity are removed. Very useful for queries that joins other entities, possibly causing extra rows in the result set.
This transformer produces a list of IDictionary instances (Hashtable), where the keys are the column aliases and the values the corresponding entities or scalar values. The usefulness may seem limited, but it can be used when a single query returns multiple entity types, separated into columns.
As the name implies, results just pass through this transformer. It's potentially useful when architecture requires a specified result transformer for each query.
This performs the same function as DistinctRootEntity, in that it outputs the root entity. It does not remove duplicates though.
Transforms each result row from the usual object[] into a List<object>, which can be slightly more convenient to work with. The end result is an IList<List<object>>.
Also used in the recipe. Creates new instances of the specified type and populates its properties, mapping column aliases to property names; it's very useful for custom projections.
Works similar to AliasToBean, but instead of populating the instances using properties, it injects the result tuples directly into the target class' constructor. This is useful when we want to create instances of a type where the values only can be set in the constructor. However, this requires that the results are projected in the same order as the constructor arguments.
There may be occasions when the built-in transformers are not enough. Perhaps you want something that works similar to AliasToBean, but also needs to feed some of the values to the constructor. If so, you can design your own result transformer, by creating a class that implements the interface IResultTransformer:
public interface IResultTransformer
{
object TransformTuple(object[] tuple, string[] aliases);
IList TransformList(IList collection);
}There are only two methods needed. The first one to be called is TransformList, which accepts an IList with all result rows. This is the stage where actions, such as sorting and duplicate removal should happen. Each item in the returned list is then fed into TransformTuple, where the array of tuples (entities or values, depending on the query), together with an array of aliases (in corresponding order), can be used to produce the actual output.