Linq to NHibernate - select count problem

Given the classes A and B where

class A
{
 string Name;
 Ilist<B> BList;
}

class B
{
 string Name;
}

With FluentNH mapping, relationship is many-to-many which is HasManyToMany(x => x.B) for A. B has no reference to A. NH version is 2.1.2.4000.

What should be the linq query to select the collection where each row contains B.Name and count of A's containing that B? Result must be the List of anonymous type who has 2 fields: Name and Count. Result also should include all 开发者_StackOverflow社区B's, hence it should be outer join.

My intend is to get the result with minimum round-trips to database, possibly in one go.

If you want to do it in Linq in one hit in code, you could do this...

var result = Session.Linq<A>()
    .SelectMany(a => a.BList, (a, b) => new { b.Name, A = a.Id })
    .ToList()
    .GroupBy(x => x.Name)
    .Select(x => new { Name = x.Key, Count = x.Count() })
    .ToList();

NHibernate.Linq (2.1.2.4000) can't handle a GroupBy after a SelectMany it seems, so the first ToList pulls all the data into memory. This is inefficient -- a SQL count would be better.

Alternatively, you could add a lazy loaded collection to your B class that goes back to A. If you're using a many-to-many table in the middle, that should be easy.

public class B
{
    public virtual string Name { get; set; }
    public virtual IList<A> AList { get; private set; }
}

Your query simply becomes...

var result = Session.Linq<B>()
    .Where(b => b.AList.Count > 0)
    .Select(b => new { b.Name, b.AList.Count }
    .ToList();

Which produces very efficient SQL from Linq (using a count) and gives the same result.