NHibernate/LINQ - Aggregate query on subcollection

Querying child collections has been a recurring issue in our applications where we use NHibernate (via LINQ). I want to figure out how to do it right. I just tried forever to get this query to work efficiently using LINQ, and gave up. Can someone help me understand the best way to do something like this?

Model: ServiceProvider
            HasMany->ServicesProvided

The gotcha here is that the HasMany is mapped as a component, so I can't directly query the ServicesProvided. For posterity's sake, here's the mapping:

    public S开发者_开发问答erviceProviderMap()
    {
        DiscriminatorValue(ProfileType.SERVICE_PROVIDER.ID);

        HasMany(p => p.ServicesProvided)
            .Table("ServiceProvider_ServicesProvided")
            .KeyColumn("ProfileID")
            .Component(spMapping =>
            {
                spMapping.Map(service => service.ID)
                    .Not.Nullable();
            })
            .AsBag();
    }

The query I am trying to create would return a collection of the count of each service that is provided. IE: Service1 -> 200, Service2 -> 465, etc.

I was able to get the query working using HQL, so here it is. Note that it just returns the ID of the service that is provided:

    select service.ID, count(service) 
    from ServiceProvider as profile 
    inner join profile.ServicesProvided as service 
    group by service.ID

I was able to get the query "working" using LINQ, but it performed atrociously. Here's the code I used (warning - it's ugly).

    Func<ServiceProvider, IEnumerable<ServicesProvided>> childSelector = sp => sp.ServicesProvided;
    var counts = this._sessionManager.GetCurrentSession().Linq<ServiceProvider>()
        .Expand("ServicesProvided")
        .SelectMany(childSelector, (t, c) => new { t = t, c = c })
        .Select(child => child.c)
        .GroupBy(sp => sp.ID)
        .Select(el => new { serviceID = el.Key, count = el.Count() });

I would love to learn how to do this correctly, please.

---

Short of going with HQL, the most elegant solution I can think of would be using a Criteria object. The following will give you what you need and with very low overhead:

ICriteria criteria = this._sessionManager.GetCurrentSession().CreateCriteria(typeof(ServiceProvider), "sp");

//set projections for the field and aggregate, making sure to group by the appropriate value
criteria.CreateAlias("sp.ServicesProvided", "s", JoinType.LeftOuterJoin)
   .SetProjection(Projections.ProjectionList()
      .Add(Projections.Property("s.ID"), "serviceID")
      .Add(Projections.Count("sp.ID"), "count")
      .Add(Projections.GroupProperty("s.ID")));

IList<object[]> results = criteria.List();

foreach (object[] entry in results)
{
   int id = (int)entry[0], qty = (int)entry[1];

   //Do stuff with the values
}