nHibernate count(*) on existing queryover

Question

I'm using nHibernate 3.3. I'd like to execute SQL like this:

select COUNT(*) from (
SELECT YEAR(MeasureDateLocal) As [Year], MONTH(MeasureDateLocal) As [MONTH], DAY(MeasureDateLocal) As [DAY], sum(this_.RainCounterValue) as y3_
FROM DriverPeriphMeasure this_
WHERE this_.IdDriver = @p1
GROUP BY YEAR(MeasureDateLocal), MONTH(MeasureDateLocal), DAY(MeasureDateLocal) ) s

but using QueryOver or LINQ. Currently I have this ugly piece code:

var countQuery = Context.Session.CreateSQLQuery(
    @"select COUNT(*) from 
    (SELECT YEAR(MeasureDateLocal) As [Year], MONTH(MeasureDateLocal) As [MONTH], DAY(MeasureDateLocal) As [DAY], sum(this_.RainCounterValue) as y3_ 
    FROM DriverPeriphMeasure this_ 
    WHERE this_.IdDriver = :driverID
    GROUP BY YEAR(MeasureDateLocal), MONTH(MeasureDateLocal), DAY(MeasureDateLocal) ) s" )
.SetParameter<Guid>( "driverID", driver );
int total = countQuery.UniqueResult<int>();

But I'd love to see how to do it using QueryOver. I managed to compose following QueryOver:

var q3 = Context.Session.QueryOver<DriverPeriphMeasure>().Where( x => x.Driver.Id == driver )
                    .SelectList( list => list
                        .Select( Projections.SqlGroupProjection( "YEAR(MeasureDateLocal) As [Year]", "YEAR(MeasureDateLocal)", new[] { "YEAR" }, new IType[] { NHibernateUtil.Int32 } ) )
                        .Select( Projections.SqlGroupProjection( "MONTH(MeasureDateLocal) As [MONTH]", "MONTH(MeasureDateLocal)", new[] { "MONTH" }, new IType[] { NHibernateUtil.Int32 } ) )
                        .Select( Projections.SqlGroupProjection( "DAY(MeasureDateLocal) As [DAY]", "DAY(MeasureDateLocal)", new[] { "DAY" }, new IType[] { NHibernateUtil.Int32 } ) )
                        );

But I can't find a way to set it as subquery.

Any way you can rewrite this to not have the inner `select`? QueryOver doesn't do well with selecting from arbitrary table expressions... — Andrew Whitaker, Jul 21 '12 at 15:43
I'm affraid I can't remove the inner select: I need to count grouped data. — ZmorzynskiK, Jul 21 '12 at 18:00
QueryOver dows not support expressions in the from clause. Linq doesnt have this limitation. — Firo, Jul 23 '12 at 10:03
Firo, it could not work even in nh-linq, because of this issue: https://nhibernate.jira.com/browse/NH-3154 and https://nhibernate.jira.com/browse/NH-3155 — hazzik, Jul 23 '12 at 16:19

score 2 · Accepted Answer · answered Jul 23 '12 at 16:33

Currently it is possible to do only with HQL. As Andrew Whitaker mentioned NH does not support subselects in select clasue. However it is possible to convert subselect in select clause to subselect in from clause using in expression. So for grouping you have to select max(id) from subselect, but as limitation of SQL at the in clause you could use subselect returning only one column.

But in Criteria and QueryOver API it is not possible to exclude grouping fields from select clause ( https://nhibernate.jira.com/browse/NH-1426 )

And this is not possible to do by linq, because of these two issues: https://nhibernate.jira.com/browse/NH-3154 and https://nhibernate.jira.com/browse/NH-3155

So you have last chance to do it with HQL:

var count = session.CreateQuery(
    @"select count(dm.Id) 
      from DriverPeriphMeasure dm 
      where dm.Id in (
          select max(dm1.Id) 
          from DriverPeriphMeasure dm1 
          where dm1.IdDriver = :? 
          group by date(dm1.MeasureDateLocal)
      )").UniqueResult();

Thank you very much for explanation. It's unfortunate, that this case isn't covered by nhibernate properly. I wonder how it looks in EF4/EF5? Maybe it's time to switch from nhibernate to EF... Anyway, is your HQL solution better than SQL solution that I posted? I must admit that in SQL execution plan it looks a little more complicated than mine. — ZmorzynskiK, Jul 23 '12 at 19:50

nHibernate count(*) on existing queryover

1 Answers1