NHibernate/LINQ - Aggregate query on subcollection -

querying child collections has been recurring issue in our applications use nhibernate (via linq). want figure out how right. tried forever query work efficiently using linq, , gave up. can me understand best way this?

model: serviceprovider             hasmany->servicesprovided 

the gotcha here hasmany mapped component, can't directly query servicesprovided. posterity's sake, here's mapping:

    public serviceprovidermap()     {         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 trying create return collection of count of each service provided. ie: service1 -> 200, service2 -> 465, etc.

i able query working using hql, here is. note returns id of service provided:

    select service.id, count(service)      serviceprovider profile      inner join profile.servicesprovided service      group service.id 

i able query "working" using linq, performed atrociously. here's code 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 love learn how correctly, please.

short of going hql, elegant solution can think of using criteria object. following give need , low overhead:

icriteria criteria = this._sessionmanager.getcurrentsession().createcriteria(typeof(serviceprovider), "sp");  //set projections field , aggregate, making sure group 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 values }