NHibernate QueryOver: Get a row count with group by in a subquery

Question

I'm trying to get a count from a query with a group by and just can't figure out how to translate the SQL I want into NHibernate's QueryOver syntax.

This is the SQL:

select count(*) from
       (select Email from Entry
       where (conditions...)
       group by Email) as tmp

Seems simple right?

This is how I'm trying to do it, but the RowCount() call seems to optimize the group by away completely:

    var query = _unitOfWork.CurrentSession.QueryOver<ContestEntry>()
        .Select(Projections.Property<ContestEntry>(x => x.Email),
                Projections.Group<ContestEntry>(x => x.Email));

    return query.RowCount();

I wouldn't mind using Criteria for this, but I'm excited about the new (to me) QueryOver API that lets me get away from magic strings.

Update:

I'm unable to use generated SQL that does a distinct query within a count (e.g. select count(distinct Email)) as this app runs on SQL CE.

See: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/80a1d7dd-22be-4583-b8f2-fcd8cde5ec53/ and http://our.umbraco.org/wiki/install-and-setup/sql-server-ce-4-known-issues ("Distinct in count is not supported", about 2/3 of the way down the page)

Answer 1

I'm not sure why you need such a complex query. If you only want the count of distinct emails meeting certain criteria I think you could use something like this in SQL:

select count(distinct email)
from Entry
where (conditions...)

And translating this to NHibernate's QueryOver API would look something like this:

int count = session.QueryOver<ContestEntry>()
            .Select(Projections.CountDistinct<ContestEntry>(x => x.Email))
            .FutureValue<int>()
            .Value;//query is not executed until here

Unless I'm missing something, I think this will get the result you're after. There is also a "Distinct" projection and a .ToRowCountQuery() method that you might find interesting.