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

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

Thanks a lot, but it gave me the following error : Unable to perform find[SQL: SQL not available]
Oops, it worked after I changed the .List() to List<object>.... Thank you a lot Mike. You saved my day.
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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related