c# – 将SQL转换为QueryOver以获取具有group bys的行计数

发布时间：2020-12-16 02:01:53 所属栏目：百科来源：网络整理

导读：我有以下NHibernate QueryOver查询： var query = session.QueryOverIssuanceReportLogEntity() .Where(i = i.CustomerId == customer.Id i.RollbackIssuanceId == null);if (Parms.StartDate != null) query.Where(i = i.IssuanceDateCreated = Parms.Start

我有以下NHibernate QueryOver查询：

var query = session.QueryOver<IssuanceReportLogEntity>()
                        .Where(i => i.CustomerId == customer.Id && i.RollbackIssuanceId == null);

if (Parms.StartDate != null) query.Where(i => i.IssuanceDateCreated >= Parms.StartDate);
if (Parms.EndDate != null) query.Where(i => i.IssuanceDateCreated <= Parms.EndDate);
if (Parms.GroupId != null) query.Where(i => i.RecipientGroupId == Parms.GroupId);
if (Parms.ProgramId != null) query.Where(i => i.ProgramId == Parms.ProgramId);

query.Select(
    Projections.Group<IssuanceReportLogEntity>(x => x.RecipientGroupId).WithAlias(() => receiver.RecipientGroupId),Projections.Group<IssuanceReportLogEntity>(x => x.RecipientId).WithAlias(() => receiver.RecipientId),Projections.Group<IssuanceReportLogEntity>(x => x.RecipientFullName).WithAlias(() => receiver.RecipientFullName),Projections.Group<IssuanceReportLogEntity>(x => x.RecipientEmployeeNumber).WithAlias(() => receiver.RecipientEmployeeNumber),Projections.Group<IssuanceReportLogEntity>(x => x.RecipientTitle).WithAlias(() => receiver.RecipientTitle),Projections.Count<IssuanceReportLogEntity>(x=>x.RecipientGroupId).WithAlias(()=>receiver.RecognitionTotalReceived),Projections.Sum<IssuanceReportLogEntity>(x=>x.Points).WithAlias(()=>receiver.TotalPoints));

if (customer.Settings.PointsEnabled)
{
    query.OrderBy(Projections.Sum<IssuanceReportLogEntity>(x => x.Points)).Desc();
}
else
{
    query.OrderBy(Projections.Count<IssuanceReportLogEntity>(x => x.InitiatorId)).Desc();
}

query.TransformUsing(Transformers.AliasToBean<TopReceiver>());

这会生成以下查询(对于数据的选择是正确的)：

SELECT TOP (20 /* @p0 */) this_.RecipientGroupId        as y0_,this_.RecipientId             as y1_,this_.RecipientFullName       as y2_,this_.RecipientEmployeeNumber as y3_,this_.RecipientTitle          as y4_,count(this_.RecipientGroupId) as y5_,sum(this_.Points)             as y6_
FROM   [IssuanceReportLog] this_
WHERE  (this_.CustomerId = '30a678bc-264a-4a04-aac4-a3270158929f' /* @p1 */
      and this_.RollbackIssuanceId is null)
     and this_.RecipientGroupId = '2fd9ec20-e870-42f6-b345-a3270158992a' /* @p2 */
GROUP  BY this_.RecipientGroupId,this_.RecipientId,this_.RecipientFullName,this_.RecipientEmployeeNumber,this_.RecipientTitle
ORDER  BY sum(this_.Points) desc

我需要做的是弄清楚如何让NHibernate生成行计数而不删除Group By,基本上是做类似的事情(注意前面的查询本质上是一个没有TOP的子查询)：

SELECT COUNT(*) FROM (
SELECT this_.RecipientGroupId        as y0_,sum(this_.Points)             as y6_
FROM   [IssuanceReportLog] this_
WHERE  (this_.CustomerId = '30a678bc-264a-4a04-aac4-a3270158929f' /* @p1 */
        and this_.RollbackIssuanceId is null)
       and this_.RecipientGroupId = '2fd9ec20-e870-42f6-b345-a3270158992a' /* @p2 */
GROUP  BY this_.RecipientGroupId,this_.RecipientTitle
) AS Query

每当我试图让一个rowcount工作时,NH就会撕掉GROUP BY.上面的SQL就像我期望的那样工作.

有关如何让NHibernate吐出SQL的任何想法？

解决方法

NHibernate中的“标准”方式是创建克隆查询：

var rowCountQuery = query.ToRowCountQuery();

哪个(来自文档)：

Clones the QueryOver,removes orders and paging,and projects the row-countfor the query

但是你已经(肯定)已经在这里经历了…这将导致纯粹的查询(因为所有必要条件都被删除了,请参阅下面的查询) – 返回错误的结果

SELECT Count(*) FROM   [IssuanceReportLog] -- notwhat needed

解决方案：

将神奇的sql片段注入投影：

COUNT(*) OVER() AS TotalRowCount

这将完全返回我们需要的东西.我们查询的总行数.我们必须扩展DTO：

public class TopReceiver
{
    ...
    public virtual int TotalRowCount { get; set; }

并像这样调整投影

query.Select(
    ... // all the GROUP BY statements
    // the total row count
    Projections.SqlProjection(" COUNT(*) OVER() AS TotalRowCount ",new string[] { "TotalRowCount" },new IType[] { NHibernateUtil.Int32 })
    // count,sum
    Projections.Count<IssuanceReportLogEntity>(x=>x.RecipientGroupId)
               .WithAlias(()=>receiver.RecognitionTotalReceived),Projections.Sum<IssuanceReportLogEntity>(x=>x.Points)
               .WithAlias(()=>receiver.TotalPoints)
);

以后我们甚至可以应用分页,但TotalRowCount的值仍然是正确的.

query
    .Skip(100)
    .Take(25)

现在,每个(包括第一个)结果都有关于总行数的信息.

var rowCount = list[0].TotalRowCount;

注意：你知道吗？这实际上是获得行数的最有效方法.不仅在一个server-db往返中,甚至在一个sql语句执行中……

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!