CODEWITHSUNDEEP
c#sqlsql-servernhibernate
Joe Young
Joe Young

Reputation: 1116

Converting SQL to QueryOver for getting a row count with group bys

I have the following NHibernate QueryOver query:

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

This generates the following query (which is correct for the selection of data):

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

What I need to do is to figure out how to get NHibernate to generate a row count WITHOUT ripping out the Group By, essentially to do something like (notice the previous query is essentially a subquery without the 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

Every time I try to get a rowcount to work, NH rips out the GROUP BY. The above SQL works as I expect it to.

Any ideas on how to get NHibernate to spit out that SQL?

Upvotes: 3

Views: 930

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

The "standard" way in NHibernate, is to create a clone query:

var rowCountQuery = query.ToRowCountQuery();

Which does (from docs):

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

But as you've (for sure) already experienced here... that would result in pure query (because all essential was removed, see the query below) - returning wrong result

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

The SOLUTION:

Inject the magical sql snippet into projection:

COUNT(*) OVER() AS TotalRowCount

This will return exactly what we need. The total row count over our query. We have to extend the DTO:

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

And adjust the projection like this

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

later we can even apply paging, but the value of TotalRowCount will still be correct.

query
    .Skip(100)
    .Take(25)

And now, each (including the first) result has the info about the total row count. 

var rowCount = list[0].TotalRowCount;

NOTE: Do you know what? This is in fact the most efficient way how to get row-count. Not only in one server-db round trip, but even in one sql statement execution...

Upvotes: 7

Related Questions