You might have noticed that the last post in the series always projects each result row into an
object. This might have made you wonder if there’s a better way to get results from a QueryOver query. Well there is! It’s called transforming.
In the context of an NHibernate query, a transformer is simply a class that transforms each row from a query into an instance of an object. NHibernate comes with several and allows you to easily create a custom transformer if you’d like.
This article is part of an ongoing series on NHibernate Queryover. Click here to see the table of contents.
Transformers are supplied to the
TransformUsing function on an instance of
IQueryOver<TRoot, TSubtype>. For example, here’s how you would use
Transformers.DistinctRootEntity (which I’ll go into more detail later about):
1 2 3 4
Using the built-in transformers
NHibernate supplies several built-in transformers in the
NHibernate.Transform namespace. These may be all you need in your application since they cover most use cases. I’ll go over each built-in transformer and how to use it.
This transformer works the way you’d think it would: it transforms the query results into a list of distinct entities of the root type. What’s the root type? Well if you read part 1, you’ll remember that a QueryOver query deals with two types,
TSubType. the root type is simply
For example, here’s a query that returns a list of all
1 2 3 4
As you can see, using
DistinctRootEntity allows us to get a list of entities easily. This example doesn’t address the distinct part of
DistinctRootEntity. Here’s another, more interesting example:
1 2 3 4 5
This is more interesting because a
Product might have many related rows in
TransactionHistory. The join would cause each
Product to appear as many times as it has
TransactionHistory records, which we probably don’t want if we’re just trying to find all
Products that were ever priced over $2.00.
Here’s the SQL the above query generates:
1 2 3 4 5 6 7 8 9 10 11 12
The result we get back is a list of distinct
DistinctRootEntity is most useful if you have a simple query in which you need instances of an entity and may or may not want to do filtering on some related entities.
This transformer allows you to transform each row of the result set into an
IDictionary (hash table). Unfortunately it’s not a generic
IDictionary. The keys are strings containing the aliases you defined in the query, and the values are entities. This is best explained with an example:
1 2 3 4 5 6 7 8 9
Each item in
results is an
IDictionary’s keys are the aliases we assigned while building our query. For example, if you wanted to get the first row’s
TransactionHistory entity, you would write:
This might seem a bit odd at first, but using
AliasToEntityMap can prove useful if you need to retrieve multiple entities in a single query.
This transformer appears to be quite similar to
AliasToEntityMap in that it generates a collection of entities for each row in the resultset. I say “appears” because I haven’t had much experience with it and I cannot find much about it online. I’ll add to this post if I come across anything interesting.
Anyway for a simple example it seems to place an instance of an entity from the query in a slot in an
object array in reverse order from when it was added to the query. For example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
As you can see,
result is a
result is a
result is the
RootEntity is similar to
DistinctRootEntity in that it projects a list of
TRoot. The difference is that the results are not distinct. Therefore if you join on a related table that multiplies the root entity, you’ll get back that entity many times for each related row. Here’s the example from
DistinctRootEntity again, except using
1 2 3 4 5
This will return any
Products with a
TransactionHistory that has an
ActualCost over $2.00, but will not remove duplicate
This transformer works very similarly to not specifying a transformer at all and getting back an
IList<object>. The difference here is that you’ll get back an
1 2 3 4 5 6 7 8 9 10 11 12 13 14
In my experience, this transformer is by far the most useful. It allows you to transform each row into an instance of a type you specify. You can project columns from different entities into properties on each instance.
AliasToBean to get a list of
HighestProductReviewDTOs. Here’s the definition for
1 2 3 4 5 6 7 8 9 10
NHibernate requires that the DTO have a parameterless constructor so that it can create an instance of your class for each row it retrieves.
We’re going to get a list of
Products that have reviews, followed by some information from that
Product’s highest review. Here’s what our query looks like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Pay particular attention to the
.WithAlias calls at the end of the
.Select calls inside of
SelectList. These are what tell NHibernate to associate particular column values in each row retrieved with the correct property in our DTO class.
In case you’re curious, here’s the SQL that NHibernate generated:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
AliasToBean is extremely useful. It allows us to specify exactly what columns we need and transform the resulting rows into instances of simple types. However it does have some limitations:
- The class you’re projecting to must have a parameterless constructor
- You cannot populate collections (e.g., if you had a class with
ProductIDand a collection of
ProductReviewsyou could not do that in one step using
- You cannot populate full entities (e.g.,
.Select(() => productReview.Product).WithAlias(() => result.Product))
While the second limitation is unfortunate, you can specify a collection type in your result class and write a separate query to populate it. You can possibly even do this in one database round trip using the
.Future method, which I’ll talk about in a later post.
AliasToBeanConstructor is similar to
AliasToBean, except that it uses a result type’s constructor to create new objects from result rows. Here’s our example from above slightly modified to use
Here’s our modified result class:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
And here’s our new QueryOver query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
AliasToBeanConstructor which we get using
GetConstructors. NHibernate calls our constructor with the column values we’re retrieving with our
SelectList. Note that all items in the
SelectList are passed to the constructor in the order you add them.
Creating your own transformer
The built in transformers are great, but if you need your own result transformer, that’s possible too.
For example, let’s say we want to call a callback function every time a row is transformed. We could also iterate over our results after retrieving them, but this gives us a way to apply any modifications we might want while we’re transforming the row. Here’s our new transformer class:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
In this example, all I’ve done is wrap
AliasToBeanResultTransformer in a class that calls the callback the user specifies after calling
TransformTuple method. I’ll use this transformer in an example that retrieves product review information but with an added property,
1 2 3 4 5 6 7 8 9 10
We can use the transformer to assign
DateRetrieved after creating a new
1 2 3 4 5 6 7 8 9 10 11 12 13 14
This is a simple example, but it should demonstrate how easy it is to extend the built in transformers. It would be nice if we could subclass the built in transformers, but unfortunately the methods we would need to override are not marked
A good place to look for how to write a transformer is the NHibernate source code itself.
I covered a lot in this post, but I was aiming to be comprehensive with each transformer type. This should enable you to effectively use the built in transformers and create your own if you need to.
- There are several built in result transformers in the
RootEntityretrieve a list of the “root” of the QueryOver query
PassThroughretrieve the entities present in the QueryOver query in an
AliasToBeanConstructorare powerful transformers that allow you to create a list of instances of a type you specify.
- You can create your own result transformer pretty easily to suit your needs.