Nhibernate query to select count of grouped by row

Question

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)

Answer 1

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();