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

我有以下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_,
                 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
) 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语句执行中……

查看更多关于c# – 将SQL转换为QueryOver以获取具有group bys的行计数的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did69303