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)

Brainpan

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.

Fasciate

Thanks Alex, you are correct - this works properly in SQL Server (and presumably other DBs). However, the app this thing is running on also uses SQL CE in some instances, and for whatever reason SQL CE does not support select count(distinct x). See social.msdn.microsoft.com/Forums/en-US/sqlce/thread/… and our.umbraco.org/wiki/install-and-setup/… – Brainpan

I marked this as the correct answer, because technically it is, as long as you're using SQL Server. For SQL CE, I had to use inline SQL to get it working. – Brainpan