Andrew Whitaker

QueryOver Series - Part 4: Transforming

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
var results =
    session.QueryOver<Product>()
        .TransformUsing(Transformers.DistinctRootEntity)
        .List<Product>();

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.

DistinctRootEntity

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, TRoot and TSubType. the root type is simply TRoot.

For example, here’s a query that returns a list of all Products:

1
2
3
4
// TRoot is Product
IList<Product> results = session.QueryOver<Product>()
    .TransformUsing(Transformers.DistinctRootEntity)
    .List<Product>();

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
IList<Product> results = session.QueryOver<Product>()
    .JoinQueryOver(pr => pr.TransactionHistory)
        .Where(th => th.ActualCost > 2.0M)
    .TransformUsing(Transformers.DistinctRootEntity)
    .List<Product>();

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
SELECT
    this_.ProductID as ProductID7_1_,
    -- All product columns
    transactio1_.TransactionID as Transact1_13_0_,
    -- All TransactionHistory columns
FROM
    Production.Product this_
inner join
    Production.TransactionHistory transactio1_
        on this_.ProductID=transactio1_.ProductID
WHERE
    transactio1_.ActualCost > 2;

The result we get back is a list of distinct Products.

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.

AliasToEntityMap

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
TransactionHistory historyAlias = null;
Product productAlias = null;

IList<IDictionary> results = session.QueryOver<Product>(() => productAlias)
    .JoinQueryOver(pr => pr.TransactionHistory, () => historyAlias)
        .Where(th => th.ActualCost > 2.0M)
    .TransformUsing(Transformers.AliasToEntityMap)
    .Take(10)
    .List<IDictionary>();

Each item in results is an IDictionary. This 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:

1
TransactionHistory history = (TransactionHistory)results[0]["historyAlias"];

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.

PassThrough

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
IList<object[]> results = session.QueryOver<Product>()
    .JoinAlias(pr => pr.Reviews, () => reviewAlias)
    .JoinQueryOver(pr => pr.TransactionHistory)
        .Where(th => th.ActualCost > 2.0M)
    .TransformUsing(Transformers.PassThrough)
    .Take(10)
    .List<object[]>();


foreach (object[] result in results)
{
    ProductReview review = (ProductReview)result[0];
    TransactionHistory t = (TransactionHistory)result[1];
    Product p = (Product)result[2];
}

As you can see, result[0] is a ProductReview, result[1] is a TransactionHistory and result[2] is the Product itself.

RootEntity

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 RootEntity:

1
2
3
4
5
IList<Product> results = session.QueryOver<Product>()
    .JoinQueryOver(pr => pr.TransactionHistory)
        .Where(th => th.ActualCost > 2.0M)
    .TransformUsing(Transformers.RootEntity)
    .List<Product>();

This will return any Products with a TransactionHistory that has an ActualCost over $2.00, but will not remove duplicate Product records.

ToList

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 IList<IList> instead.

For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Product productAlias = null;

IList<IList> results = session.QueryOver<Product>(() => productAlias)
    .JoinQueryOver(pr => pr.TransactionHistory)
        .Where(th => th.ActualCost > 2.0M)
    .TransformUsing(Transformers.ToList)
    .SelectList(list => list
        .Select(() => productAlias.Id)
        .Select(() => productAlias.Name)
    )
    .List<IList>();

Console.WriteLine(results[0][0]); // product Id
Console.WriteLine(results[0][1]); // product Name

AliasToBean

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.

Lets use AliasToBean to get a list of HighestProductReviewDTOs. Here’s the definition for HighestProductReviewDTO:

1
2
3
4
5
6
7
8
9
10
public class HighestProductReviewDTO
{
    public int ProductID { get; set; }

    public string ProductName { get; set; }

    public int Rating { get; set; }

    public string Comments { get; set; }
}

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
IList<HighestProductReviewDTO> highestReviews =
    session.QueryOver<Product>(() => productAlias)
        .JoinQueryOver(pr => pr.Reviews, () => productReviewAlias)
            .WithSubquery.Where(pr => pr.Id == QueryOver.Of<ProductReview>()
                .Where(rev => rev.Product.Id == productAlias.Id)
                .OrderBy(rev => rev.Rating).Desc()
                .Select(rev => rev.Id)
                .Take(1)
                .As<int>())
        .SelectList(list => list
            .Select(() => productAlias.Id).WithAlias(() => result.ProductID)
            .Select(() => productAlias.Name).WithAlias(() => result.ProductName)
            .Select(() => productReviewAlias.Rating).WithAlias(() => result.Rating)
            .Select(() => productReviewAlias.Comments).WithAlias(() => result.Comments)
        )
        .TransformUsing(Transformers.AliasToBean<HighestProductReviewDTO>())
        .List<HighestProductReviewDTO>();

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
SELECT
    this_.ProductID as y0_,
    this_.Name as y1_,
    productrev1_.Rating as y2_,
    productrev1_.Comments as y3_
FROM
    Production.Product this_
inner join
    Production.ProductReview productrev1_
        on this_.ProductID=productrev1_.ProductID
WHERE
    productrev1_.ProductReviewID = (
        SELECT
            TOP (1)  this_0_.ProductReviewID as y0_
        FROM
            Production.ProductReview this_0_
        WHERE
            this_0_.ProductID = this_.ProductID
        ORDER BY
            this_0_.Rating desc

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 ProductID and a collection of ProductReviews you could not do that in one step using AliasToBean)
  • 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

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 AliasToBeanConstructor instead.

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
public class HighestProductReviewDTO
{
    public HighestProductReviewDTO(
        int productId, string productName, int rating, string comments)
    {
        this.ProductID = productId;
        this.ProductName = productName;
        this.Rating = rating;
        this.Comments = comments;
    }

    public int ProductID { get; private set; }

    public string ProductName { get; private set; }

    public int Rating { get; private set; }

    public string Comments { get; private set; }
}

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
IList<HighestProductReviewDTO> highestReviews =
    session.QueryOver<Product>(() => productAlias)
        .JoinQueryOver(pr => pr.Reviews, () => productReviewAlias)
            .WithSubquery.Where(pr => pr.Id == QueryOver.Of<ProductReview>()
                .Where(rev => rev.Product.Id == productAlias.Id)
                .OrderBy(rev => rev.Rating).Desc()
                .Select(rev => rev.Id)
                .Take(1)
                .As<int>())
        .SelectList(list => list
            .Select(() => productAlias.Id)
            .Select(() => productAlias.Name)
            .Select(() => productReviewAlias.Rating)
            .Select(() => productReviewAlias.Comments)
        )
        .TransformUsing(Transformers.AliasToBeanConstructor(
            typeof(HighestProductReviewDTO).GetConstructors().First()))
        .List<HighestProductReviewDTO>();

We’re passing ConstructorInfo to 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
/// <summary>
/// A result transformer that calls a callback after successfully transforming a result row 
/// into an instance of T
/// </summary>
/// <typeparam name="T">The result type</typeparam>
public class AliasToBeanWithCallbackTransformer<T> : IResultTransformer
{
    private readonly AliasToBeanResultTransformer aliasToBeanTransformer;
    private readonly Action<T> callback;

    public AliasToBeanWithCallbackTransformer(Action<T> callback)
    {
        this.aliasToBeanTransformer = new AliasToBeanResultTransformer(typeof(T));
        this.callback = callback;
    }

    public IList TransformList(IList collection)
    {
        return this.aliasToBeanTransformer.TransformList(collection);
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        object result = this.aliasToBeanTransformer.TransformTuple(tuple, aliases);

        // Call the callback before returning the result.
        callback((T)result);

        return result;
    }
}

In this example, all I’ve done is wrap AliasToBeanResultTransformer in a class that calls the callback the user specifies after calling AliasToBeanResultTransformer’s TransformTuple method. I’ll use this transformer in an example that retrieves product review information but with an added property, DateRetrieved:

1
2
3
4
5
6
7
8
9
10
public class ProductReviewDTO
{
    public int ProductReviewID { get; set; }

    public int Rating { get; set; }

    public string Comments { get; set; }

    public DateTime DateRetrieved { get; set; }
}

We can use the transformer to assign DateRetrieved after creating a new ProductReviewDTO:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DateTime dateRetrieved = DateTime.Now;

IList<ProductReviewDTO> highestReviews =
    session.QueryOver<ProductReview>()
        .SelectList(list => list
            .Select(pr => pr.Comments).WithAlias(() => result.Comments)
            .Select(pr => pr.Id).WithAlias(() => result.ProductReviewID)
            .Select(pr => pr.Rating).WithAlias(() => result.Rating)
        )
        // Assign "DateRetrieved correctly:
        .TransformUsing(new AliasToBeanWithCallbackTransformer<ProductReviewDTO>(
            hp => hp.DateRetrieved = dateRetrieved))
        .Take(10)
        .List<ProductReviewDTO>();

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 virtual.

A good place to look for how to write a transformer is the NHibernate source code itself.

Summary

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 NHibernate.Transform namespace.
  • DistinctRootEntity and RootEntity retrieve a list of the “root” of the QueryOver query
  • AliasToEntityMap and PassThrough retrieve the entities present in the QueryOver query in an IDictionary and object[], respectively.
  • AliasToBean and AliasToBeanConstructor are 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.

Comments