NHibernate select a list of objects with related child objects with QueryOver

Question

I'm having trouble with something that is probably very simple. In my database I have the following tables:

tblOrder
-----------------
Id
OrderStatusId

tblOrderStatus
-----------------
Id
Name

And I have made the following mappings in my project:

[Class(NameType = typeof(Order), Table = "tblOrder")
public class Order {
    [Id(-2, Name = "Id")]
    [Generator(-1, Class = "native")]
    public virtual long Id { get; set; }

    [ManyToOne]
    public virtual OrderStatus Status { get; set; }
}

[Class(NameType = typeof(OrderStatus), Table = "tblOrderStatus")]
public class OrderStatus {
    [Id(-2, Name = "Id")]
    [Generator(-1, Class = "native")]
    public virtual long Id { get; set; }

    [Property]
    public virtual string Name { get; set; }
}

The query should return a IList<OrderSummary>. I want the class OrderSummary to have a property Status where Status is an object with an Id and a Name property. This could be either with a KeyValuePair or of type OrderStatus (whichever is best and works). Fetching the orders is not a problem but adding the OrderStatus as an object with said properties is the part I'm having trouble with. I also need to return the result of the query as JSON to the client.

OrderSummary should look like this:

public class OrderSummary {
    public long Id { get; set; }
    public OrderStatus Status { get; set; }
}

In my first version OrderSummary had separate properties for OrderStatusId and OrderStatusName. This works but I'm trying to avoid these separate properties. I have also tried this with SelectSubQuery but this returns an error because it returns more than one field in a subquery.

----------------------------------- UPDATE -----------------------------

Following Fredy Treboux's advice I changed my query using Eager which result in the following query:

var query = session.QueryOver<OrderStatus>
    .Fetch(o => o.Status).Eager
    .JoinAlias(o => o.Status, () => statusAlias, JoinType.LeftOuterJoin);

The problem is, I found out, is not selecting the data but how to convert the retrieved Status and assign it to OrderSummary.Status? I have tried the following:

OrderSummary orderAlias = null;
query.SelectList(list => list
    .Select(o => o.Id).WithAlias(() => orderAlias.Id)
    .Select(() => statusAlias).WithAlias(() => orderAlias.Status)
).TransformUsing(Transformer.AliasToBean<OrderSummary>());

-------------------------------- ANSWER ----------------------------------

As I said in my last edit, the problem does not seem to be the actual selection of OrderStatus but returning it to the client. So I thought it was my lack of knowledge of NHibernate instead it was as simple as adding the [JsonObject] attribute to the OrderStatus class. How silly of me.

I have changed my query to the following:

Order orderAlias = null;
OrderSummary orderSummary = null;
OrderStatus statusAlias = null;
var query = session.QueryOver<Order>(() => orderAlias)
    .JoinAlias(() => orderAlias.Status, () => statusAlias, JoinType.LeftOuterJoin);

query = query
    .Select(
        Projections.ProjectionList()
            .Add(Projections.Property(() => orderAlias.Id).WithAlias(() => orderSummary.Id))
            .Add(Projections.Property(() => orderAlias.Status).WithAlias(() => orderSummary.Status)
    );
Result = query.TransformUsing(Tranformers.AliasToBean<OrderSummary>())
    .List<OrderSummary>()
    .ToList();

Answer 1

I'm afraid that currently it's not possible. I guess that Nhibernate transformers are not able to construct nested complex properties. You can return list of tuples and then cast it manually to your entity:

OrderStatus statusAlias = null;        

var tuples = Session.QueryOver<Order>()
            .JoinQueryOver(x => x.Status, () => statusAlias)
            .SelectList(list => list
                .Select(x => x.Id)
                .Select(x => statusAlias.Id)
                .Select(x => statusAlias.Name))
            .List<object[]>();

var result = tuples.Select(Convert);

private OrderSummary Convert(object[] item) {
            return new OrderSummary
            {
                Id = (long)item[0],
                OrderStatus = new OrderStatus { Id = (long)item[1], Name = (string)item[2] }
            };
        }

Also if you don't bother about performance much it's possible to fetch a list of you Orders and convert it to OrderSummary. You can do it by simply define casting operator or using some tool like AutoMapper or ExpressMapper.

Answer 2

Sorry I didn't see your comment asking for an example before. I'm going to leave some code explaining the approach I mentioned, although it was already given as an alternative in the other response and I believe it's the easiest way to go (not using transformers at all):

string GetOrderSummaries()
{
   // First, you just query the orders and eager fetch the status.
   // The eager fetch is just to avoid a Select N+1 when traversing the returned list.
   // With that, we make sure we will execute only one query (it will be a join).
   var query = session.QueryOver<Order>()
                      .Fetch(o => o.Status).Eager;

   // This executes your query and creates a list of orders.
   var orders = query.List();

   // We map these orders to DTOs, here I'm doing it manually.
   // Ideally, have one DTO for Order (OrderSummary) and one for OrderStatus (OrderSummaryStatus).
   // As mentioned by the other commenter, you can use (for example) AutoMapper to take care of this for you:
   var orderSummaries = orders.Select(order => new OrderSummary
   {
      Id = order.Id,
      Status = new OrderSummaryStatus
      {
         Id = order.Status.Id,
         Name = order.Status.Name
      }
   }).ToList();

   // Yes, it is true that this implied that we not only materialized the entities, but then went over the list a second time.
   // In most cases I bet this performance implication is negligible (I imagine serializing to Json will possibly be slower than that).
   // And code is more terse and possibly more resilient.

   // We serialize the DTOs to Json with, for example, Json.NET
   var orderSummariesJson = JsonConvert.SerializeObject(orderSummaries);
   return orderSummariesJson;
 }

Useful links:
AutoMapper: http://automapper.org/
Json.NET: http://www.newtonsoft.com/json