Nhibernate query to select count of grouped by rows

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)

Mike · Accepted Answer · 2012-11-19 12:51:00Z

7

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

answered Nov 19, 2012 at 12:51

Mike

4,2973 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nour Over a year ago

Thanks a lot, but it gave me the following error : Unable to perform find[SQL: SQL not available]

Nour Over a year ago

Oops, it worked after I changed the .List() to List<object>.... Thank you a lot Mike. You saved my day.

Nour Over a year ago

is there an easy way to cast the result to Dictionary<StatusEnum, int> ?

Collectives™ on Stack Overflow

Nhibernate query to select count of grouped by rows

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related