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

c#, nhibernate, sql, sql-server

Solution

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

Problem

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?

Original source