Nhibernate查询以选择按行分组的计数 [英] Nhibernate query to select count of grouped by rows

问题描述

我需要使用 NHibernate 获取此查询:

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]

这是我的课程:

 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; }
}

我试过这个查询:

   var lst = uow.Session.QueryOver<ApprovalRequest>()
                .JoinQueryOver<RequestStatus>(req => req.CurrentStatus)
                .SelectList(list =>
                    list
                    .SelectCount(p => p.Id)
                    .SelectGroup(p => p.CurrentStatus.Status)
                    ).List();

但我得到了一个错误:

无法解析属性:CurrentStatus.Status of: ApprovalRequest (NHibernate)

could not resolve property: CurrentStatus.Status of: ApprovalRequest (NHibernate)

推荐答案

您需要将您的联接表绑定到别名.

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

这篇关于Nhibernate查询以选择按行分组的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！