Getting orders and the count of orderlines using Linq

When using NHibernate we sometimes need to pick the right tool from the toolbox. One example of this is when you have the follow problem.

I need to get a list of orders and the count of order items against each order.

Now me being a bit savvy with QueryOver would sit down and would write a complex piece of code. This code doesn't roll out the top of your head and you need to fit a few pieces together to get it right.

Another option is to use the NHibernate built in Linq provider. In my opinion before NH3.1 the Linq provider just was not quite up to the job and people shied away from it. However the NHibernate team has done a lot of work on the built in Linq provider and it is now maturing into a useful tool.

So back to the solution to our problem, simply first create a DTO:-

public class OrderDto
{
    public long Id { get; set; }
    public DateTime OrderDate { get; set; }
    public int LineCount { get; set; }
}

I would always try and project into a DTO rather than using filters or formula's as I try to keep my mappings as clean as possible. This gives me better flexibility and does not back me into a corner.

The linq query is very easy:-

return Session.Query<Order>()
        .Select(o => new OrderDto
        {
            Id = o.Id,
            OrderDate = o.DateOfOrder,
            LineCount = o.ActorList.Count
        })
    .ToList();

This would produce the following SQL:-

select o.Id, o.OrderDate,
       (select cast(count(*) as INT) from  OrderLines
          where  Order.Id = OrderLines.orderId) as LineCount
from   Order o

I am going to look more closely into the built in NHibernate Linq provider as it can produce some easy and beautiful code.