NHibernate's non-stringly-typed LEFT JOIN

As of the time of this writing (and since 2009), NHibernate Linq doesn't support DefaultIfEmpty(), which is a LEFT JOIN functionality. And as such, we won't be able to report entities with collections having count of zero, as that kind of report uses left join.



Given this domain class:

public class Product
    {
        public virtual int ProductId { get; set; }
        public virtual string ProductName { get; set; }
        public virtual string ProductDescription { get; set; }
        public virtual int YearIntroduced { get; set; }

        public virtual IList<Order> Orders { get; set; }
    }

The way to report product even with no orders:

Order orderAlias = null;
var results =
    s.QueryOver<Product>()
    .Left.JoinAlias(od => od.Orders, () => orderAlias)
    .SelectList(selection => 
        selection.SelectGroup(b => b.ProductId)
            .SelectGroup(b => b.ProductName)
        .SelectCount(b => orderAlias.OrderId))                    
    .List<object[]>()                    
    .Select(zz => new
    {
        ProductId = (int)zz[0],
        ProductName = (string)zz[1],
        OrderCount = (int)zz[2]
    });

foreach (var item in results)
{
    Console.WriteLine("{0} {1}", item.ProductName, item.OrderCount);
}

That doesn't materialize the product orders to List<Order>. That's efficient, it uses COUNT, LEFT JOIN, GROUP BY.

However, the ugly consequence of using IQueryOver (aside from IQueryOver approach is also ugly) is we won't be able to use the more mockable and unit-test-friendly IQueryable. We can use IQueryable even on in-memory objects which makes IQueryable amenable for unit-testing. IQueryOver API looks very convoluted as compared to Linq's API simplicity; to contrast, here's how it looks like if NHibernate's Linq supported DefaultIfEmpty:

var www = 
    from p in s.Query<Product>
    from o in p.Orders.DefaultIfEmpty()
    group o by new { p.ProductId, p.ProductName } into grp
    select new { grp.Key.ProductId, grp.Key.ProductName, OrderCount = grp.Sum(x => x.Product != null ? 1 : 0 ) };