Can you perform complex aggregate operations using NH3?

Question

I need to perform a grouping aggregation similar to something like this in T-SQL:

select b.Name as [Grouped Name],
COUNT(distinct a.ID) as [Grouped Count], 
COUNT(distinct c1.ID) as [Second Group Count], 
COUNT(distinct c2.ID) as [Third Group Count]
from TableA a
left join TableB b on a.ID = b.TableAID
left join TableC c1 on a.ID = c1.TableAID and c1.SomeCondition = 1
left join TableC c2 on a.ID = c2.TableAID and c2.SomeCondition = 2
group by b.Name
order by b.Name

I have made a number of attempts at trying to build an object model and then map it using NH3, and then build a query which will build this SQL syntax, but I've had little success.

Is this something which is possible to do using NH3? If so, is there a way to do it in LINQ? or Criteria API? or HQL?

At this point I'm open to anything. I just need to be pointed in the right direction because all the rabbit holes I've gone down on so far haven't led me anywhere.

The LINQ I've tried so far is this:

//act
var query = from a in session.Query<TableA>()
    orderby a.TableB.Name
    select
        new
        {
            Grouped Name = a.TableB.Name,
            GroupedCount = a.Count(),
            SecondGroupCount = a.TableC.Count(c => c.SomeCondition == 1),
            ThirdGroupCount = a.TableC.Count(c => c.SomeCondition == 2),
        };

var results1 = query.ToList();

I've also tried it with a group by but no matter what I do, NH3 always throws an exception if I refer to TableC in more than one aggregate. If I only have it with one aggregate, the query executes. If I try to do it with two aggregates, I get an exception.

Answer 1

HQL is probably the way to go. Linq is too leaky, Criteria is too verbose.

Something along the lines of:

select b.Name as [Grouped Name],
COUNT(distinct a.id) as [Grouped Count], 
COUNT(distinct c1.id) as [Second Group Count], 
COUNT(distinct c2.id) as [Third Group Count]
from TableA a
left join a.TableB b
left join a.TableC c1 with c1.SomeCondition = 1
left join a.TableC c2 with c2.SomeCondition = 2
group by b.Name
order by b.Name

Which is almost the same as your SQL, with minor tweaks in the joins. I'm not completely sure if the double join to TableC will work, but it doesn't hurt to try.

In any case, if the SQL works, you don't have to stop using it... CreateSQLQuery will always work.