NHibernate QueryOver<> - Aggregate function over SubQuery

How can I write the following SQL statement using QueryOver<> syntax?

SELECT COUNT(*) FROM (
    SE开发者_JAVA百科LECT FirstName,LastName 
    FROM People 
    GROUP BY FirstName, LastName
    ) as sub_t

I have the inner query working so far:

var q = _session.QueryOver<Person>()
    .SelectList(l => l
        .SelectGroup(x => x.FirstName)
        .SelectGroup(x => x.LastName));

But I have no idea how to wrap this in a subquery and get a row count out of it. Can it be done?

Unfortunately my RDBMS dialect (MsSqlCe40Dialect) does not support COUNT DISTINCT so I do not have the benefit of using SelectCountDistinct().

I am not familiar with QueryOver, but I have used the following aggregate function when a sub query was not possible for this type of count, thought it might be useful, and while posting discovered a few issues I wasn't aware of previously so I posted them too.

Note: it is about 10x slower with moderate data amounts.

Aggregate method

SELECT
COUNT(DISTINCT FirstName+LastName )
FROM People

Accommodate for special cases

similar combination names "Joe Smith" vs "Joes Mith" (Assumes ~ is not in your dataset)

SELECT
COUNT(DISTINCT FirstName+'~'+LastName )
FROM People

nulls (Assumes ^ is not in your dataset)

SELECT
COUNT(DISTINCT IsNull(FirstName,'^')+'~'+IsNull(LastName,'^') )
FROM People

Trailing white space, seems RTRIM is intrinsic to Group By

SELECT
COUNT(DISTINCT IsNull(RTrim(FirstName),'^')+'~'+IsNull(Rtrim(LastName),'^') )
FROM People

Benchmarking (80k rows of data on AMD single Quad Core)

80-100ms - run Sub Query Method (see OP)

800-1200ms - aggregate method with distinct, accommodating for special cases doesn't seem to make much noticeable difference.

Is it not possible for you to use the RowCount property of the IQueryOver? Like this:

var totalRows = _session.QueryOver<Person>()
.SelectList(l => l
    .SelectGroup(x => x.FirstName)
    .SelectGroup(x => x.LastName)).RowCount();

Ok, I don't know the reasons behing using QueryOver, but I would do something like this, I think it will give you what you are looking for:

 Session.CreateCriteria<Person>()
                .SetProjection(
                Projections.ProjectionList()
                    .Add(Projections.GroupProperty("FirstName")))
                    .Add(Projections.GroupProperty("LastName")))
                .List<Person>().Count();

Hope that helps...