c# - 将 SQL 转换为 QueryOver 以使用 group bys 获取行数

Question

我有以下 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

每次我尝试让行计数起作用时，NH 都会破坏 GROUP BY。上面的 SQL 按照我的预期工作。

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

Answer 1

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 片段注入(inject)到投影中:

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 语句执行中...