Get Count from IQueryOver without loading rows from DB

Question

Having IQueryOver, How I can get the row count from it, without loading all rows from DB? QueryOver has RowCount() method, but if the underlying query has group-by or distinct, it discards them.

***** Update *****

SQL genearted for QueryOver.RowCount (As you see it discards DISTINCT):

exec sp_executesql N'
SELECT count(*) as y0_ 
FROM dbo.OPR_STL_DCM this_ 
left outer join dbo.OPR_STL_LN_ITM lineitem1_ 
on 
this_.DCM_ID=lineitem1_.DCM_ID 
left outer join dbo.OPR_STL_DY_LN_ITM daylineite2_ 
on 
lineitem1_.DCM_ID=daylineite2_.DCM_ID and 
lineitem1_.TND_ID=daylineite2_.TND_ID 
WHERE 
daylineite2_.BSNS_DT >= @p0 and 
daylineite2_.BSNS_DT <= @p1'
,N'@p0 datetime,@p1 datetime',@p0='2016-07-22 00:00:00',@p1='2016-08-21 23:59:59'

and

SQL generated for QueryOver :

exec sp_executesql N'
SELECT distinct this_.DCM_ID as y0_, 
    this_.RTL_STR_ID as y1_, 
    this_.WS_ID as y2_, 
    this_.BSNS_DT as y3_, 
    this_.OPR_ID as y4_, 
    this_.TND_RPSTY_ID as y5_, 
    this_.IS_CNC as y6_, 
    this_.IS_SNG_DY_STL as y7_, 
    this_.BGN_DT_TM as y8_, 
    this_.END_DT_TM as y9_ 
FROM dbo.OPR_STL_DCM this_ 
left outer join dbo.OPR_STL_LN_ITM lineitem1_ 
on 
this_.DCM_ID=lineitem1_.DCM_ID 
left outer join dbo.OPR_STL_DY_LN_ITM daylineite2_ 
on 
lineitem1_.DCM_ID=daylineite2_.DCM_ID and 
lineitem1_.TND_ID=daylineite2_.TND_ID 
WHERE daylineite2_.BSNS_DT >= @p1 and 
daylineite2_.BSNS_DT <= @p2'
,N'@p1 datetime,@p2 datetime',@p0=20,@p1='2016-07-22 00:00:00',@p2='2016-08-21 23:59:59'

Answer 1

Finally I found a solution. Inject MyMsSql2008Dialect into nhibernate.dialect value. This class inserts the rows count into a temp table named #TempCount; Now you can read rows count from #TempCount afterward. Beware that this must be done in a session.

public class MyMsSql2008Dialect : MsSql2008Dialect
{
    public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
    {
        SqlString limitString = base.GetLimitString(queryString, offset, limit);

        SqlStringBuilder ssb = new SqlStringBuilder();

        string resultCountQuery = string.Format(
            @"
                INSERT INTO #TempCount
                SElECT COUNT(*) AS Count FROM 
                (
                    {0}
                ) AS _queryResult 
            "
            , queryString);

        ssb.Add(resultCountQuery);

        SqlStringBuilder newLimitString  = new SqlStringBuilder();
        newLimitString.Add(limitString).Add(Environment.NewLine).Add(ssb.ToSqlString());

        return newLimitString.ToSqlString();
    }
}

And to get rows count :

int rowsCount = session.CreateSQLQuery("SELECT TOP 1 * FROM #TempCount").UniqueResult<int>();

Answer 2

I think that is not supported with IQueryOver, but don't quote me on that.

I got it working with ICriteria though..

 crit.SetProjection(Projections.Count(Projections.Distinct(Projections.Id())));