How to modify an NHibernate query to behave like simple SQL?

Question

I'm trying to modify the following code so that it will return a Dictionary<int,int> where the keys correspond to groupId and the values correspond to the total companies in the group, instead of a List<Company>

companies = _session.CreateCriteria<Company>()
    .Add<Company>(x => x.CompanyGroupInfo.Id == groupId)
    .List<Company>();

Unfortunately, I'm not really familiar with NHibernate at all..

This is the SQL I'm supposed to base the new code on, because this SQL produces the correct result:

SELECT
      [CompanyInfo_GroupId]
      ,count([Company_Id]) TotalNumberOfCompanies
FROM 
      [Company]
      inner join [CompanyInfo]
            on [CompanyInfo].[CompanyInfo_MSCompanyId] 
                = [Company].[Company_Id]
where
      -- I have an array of GroupIds that I get the ids from 
      [CompanyInfo_GroupId] in(963, 1034) 
group by
      [CompanyInfo_GroupId]

which outputs a table as follows:

CompanyInfo_GroupId TotalNumberOfCompanies
------------------- ----------------------
963                 5
1034                1

Can somebody please give me a few pointers? Thanks

Answer 1

I think you should take a look at Projections, aggregation and grouping

Answer 2

Here's how that query might look in Criteria...

session.CreateCriteria<Company>()
    .CreateAlias("CompanyInfo", "cnfo", InnerJoin)
    .Add(Restrictions.In("cnfo.Group.id", new int[] {963, 1034}))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("cnfo.Group.id"), "CompanyInfoGroupID")
        .Add(Projections.RowCount(), "TotalNumberOfCompanies"))
    .SetResultTransformer(Transformers.AliasToBean<SomeDTO>())
    .List<SomeDTO>();

...

public class SomeDTO
{
    public int CompanyInfoGroupID { get; set; }
    public int TotalNumberOfCompanies { get; set; }
}