Projecting aggregates together with Entity using NHibernate

Question

I have a pretty standard Orders table and an associated OrderRows table, say:

Orders
[id] INTEGER
[name] ...

OrderRows
[orderId] INTEGER
[quantity] INTEGER
[unitPrice] SMALLMONEY
[description] VARCHAR(...)

For some situations I would like to retrieve a list of Orders together with a summary of totals, most of the time I don't care for the summary.

Using the Criteria API I can create a projection of [quantity]*[unitPrice] for each Order but how do I get both the Order and the projection in the same result set?

Answer 1

I believe the solution here is to create a DTO class and then use that as a result transform.

public class OrderDTO
{
    int Id;
    // Rest of Orders properties you want

    int LineTotal; // [quantity]*[unitPrice]

    public OrderDTO(int id, /* rest of constructor parameters */);
}

session.CreateCriteria(typeof(... /* criteria query here */))
    .SetResultTransformer(new AliasToBeanConstructorResultTransormer(typeof(OrderDTO).GetConstructors()[0]));

Answer 2

You can calculate Total in entity and store it in database as well (if you don't want to load all rows each time).