NHibernate QueryOver selects entity and aggregates

Question

What I want to do is display a simple data grid that contains entity data and the aggregate data of my children. For example, you can use order and position. I want to display order information and the number of items.

OrderID, OrderDate, NumOfLineItems

Now, as a rule, in SQL you can do it differently. But this is the only way I could think that this might work when translating to NHibernate.

SELECT o.OrderID, OrderDate, NumOfLineItems
FROM #Orders o
INNER JOIN
(SELECT o2.OrderID, COUNT(*) As NumOfLineItems FROM #LineItems l
INNER JOIN #Orders o2 ON o2.OrderID = l.OrderID
WHERE UserID = 1 GROUP BY o2.OrderID) t1 ON o.OrderID = t1.OrderID
WHERE UserID = 1

I know there are other ways, but I'm trying to think about how NHibernate will allow me to use the QueryOver syntax. I do not want to use derived columns. I am trying to avoid writing SQL.

For my entities, I have an Order object and an AggregatedOrder object, which will be my DTO in this case, and I plan to use an aliastobean transformer to copy data into it.

I just don’t know how to understand this.

All I have so far:

        QueryOver<LineItem> x = QueryOver.Of<LineItem>()
            .SelectList(p => p .SelectCount(l => l.Id).WithAlias(() => itemAlias.NumOfLineItems))
            .JoinQueryOver<Order>(l => l.Order)
            .Where(o => o.UserID == userID)


        var y = session.QueryOver<Listing>()
            .JoinQueryOver<Bid>(x); // no idea whats going on here

Answer 1

Given:

public class Order
{
    public virtual int OrderId {get; set;}
    public virtual DateTime OrderDate {get; set;}
    public virtual IList<LineItem> LineItems {get; set;}
}
public class LineItem
{
    public virtual int Id {get; set;}
    public virtual string Description {get; set;}
}

To query the Order + Aggregated LineItem projection using the QueryOver API, you can do the following:

OrderDto orderDto = null;
LineItem items = null;
var results = session.QueryOver<Order>()
     .JoinAlias(o => o.LineItems, () => items)
     .Select(Projections.ProjectionList()
      .Add(Projections.Property<Order>(o=>o.Id).WithAlias(()=>orderDto.OrderId))
      .Add(Projections.Property<Order>(o=>o.DateOrdered).WithAlias(()=>orderDto.DateOrdered))
      .Add(Projections.Count(()=> items.Id).WithAlias(()=>orderDto.ItemCount))
     )
     .TransformUsing(Transformers.AliasToBean<OrderDto>())
    .List<OrderDto>();