Nhibernate GroupBy multiple and Count

First off I know GroubBy multiple properties has not been implemented yet.

What I want to do is

SELECT count(*)
FROM ( 
    SELECT this_.SubmissionDate as y0_
    FROM   [Coupo开发者_C百科n] this_
    WHERE  this_.PaymentOrder_id is null 
    GROUP BY this_.SubmissionDate,
           this_.Deal_id
) AS query

the best I have in Nhibernate is

Session.QueryOver<Coupon>().Select(Projections.Group<Coupon>(e => e.SubmissionDate),Projections.Group<Coupon>(e => e.Deal.Id)).Future<object[]>().Count()

which brings the whole set and then counts it.

I have found this and created this

var count = Session.CreateQuery("select count(*) from (select c.SubmissionDate from Coupon c where c.PaymentOrder.Id is null group by c.SubmissionDate, c.Deal.Id) as query").FutureValue<Int32>();

which doesn't work. Throws a

Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 21

Exception Details: NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 21

more exception

[QuerySyntaxException: Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 21]
   NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException() +118
   NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse() +416

---

Did you try something like this, that will do the counting in memory ?

var count = Session.CreateQuery("select c.SubmissionDate from Coupon c where c.PaymentOrder.Id is null group by c.SubmissionDate, c.Deal.Id").ToList().Count;

HQL doesn't seem to support subqueries outside select and where : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-subqueries

Otherwise, you could create a stored procedure, and add it in your mapping : Correct NHibernate Mapping For Stored Procedure?