Nhibernate query to select count of grouped by rows

count, group-by, nhibernate, select, subquery

Solution

You need to bind your joined table to an alias.

ApprovalRequest approvalRequestAlias = null;
RequestStatus requestStatusAlias = null;

var lst = uow.Session.QueryOver<ApprovalRequest>(() => approvalRequestAlias)
    .JoinAlias(() => approvalRequestAlias.CurrentStatus, () => requestStatusAlias)
    .SelectList(list => list
        .SelectCount(p => p.Id)
        .SelectGroup(p => requestStatusAlias.Status)
    ).List();

Problem

I need to get this query using NHibernate: ``` Select RequestStatus.[Status], Count(ApprovalRequest.Id) From ApprovalRequest Inner Join RequestStatus On ApprovalRequest.CurrentStatusId = RequestStatus.Id Where RequestStatus.[Status] In ('Approved', 'Queried') And ApprovalRequest.Deleted != 1 Group By RequestStatus.[Status] ``` here are my classes: ``` public class ApprovalRequest { public ApprovalRequest() { Id = Guid.NewGuid(); Statuses = new List<RequestStatus>(); } /// <summary> /// Identity No. /// </summary> public Guid Id { get; set; } /// <summary> /// Represents the current status of the request e.g. Approved. /// </summary> public RequestStatus CurrentStatus { get; set; } /// <summary> /// The statuses that the request experiences. /// </summary> public IList<RequestStatus> Statuses { get; set; } } } public class RequestStatus { public RequestStatus() { Id = Guid.NewGuid(); } public Guid Id { get; set; } public StatusEnum Status { get; set; } } ``` I tried this query : ``` var lst = uow.Session.QueryOver<ApprovalRequest>() .JoinQueryOver<RequestStatus>(req => req.CurrentStatus) .SelectList(list => list .SelectCount(p => p.Id) .SelectGroup(p => p.CurrentStatus.Status) ).List(); ``` but I got an error: could not resolve property: CurrentStatus.Status of: ApprovalRequest (NHibernate)

Original source