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
– Andrew Whitaker, Commented Jul 21, 2012 at 15:43
I'm affraid I can't remove the inner select: I need to count grouped data. — ZmorzynskiK
– ZmorzynskiK, Commented Jul 21, 2012 at 18:00
QueryOver dows not support expressions in the from clause. Linq doesnt have this limitation. — Firo
– Firo, Commented Jul 23, 2012 at 10:03
Firo, it could not work even in nh-linq, because of this issue: nhibernate.jira.com/browse/NH-3154 and nhibernate.jira.com/browse/NH-3155 — hazzik
– hazzik, Commented Jul 23, 2012 at 16:19

hazzik · Accepted Answer · 2012-07-23 16:33:47Z

2

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

answered Jul 23, 2012 at 16:33

hazzik

13.4k9 gold badges51 silver badges89 bronze badges

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

2 Comments

ZmorzynskiK Over a year ago

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.

hazzik Over a year ago

I would advice you to use SQL as this HQL just a hack.

Collectives™ on Stack Overflow

nHibernate count(*) on existing queryover

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related