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

Question

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?

Radim Köhler · Accepted Answer · 2014-05-21 06:08:57Z

7

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...

answered May 21, 2014 at 6:08

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joe Young Over a year ago

That worked like a champ! I saw this technique with HQL but not with QueryOver.

Radim Köhler Over a year ago

Great to see that ;) Enjoy NHibernate

Collectives™ on Stack Overflow

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

1 Answer 1

The SOLUTION:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The SOLUTION:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related