NHibernate QueryOver<> - Aggregate function over SubQuery

Question

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

SELECT COUNT(*) FROM (
    SELECT 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().

Talbert

Answer 1

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.

Potamic

Answer 2

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

Raincoat

Unfortunately the GROUP BY is not preserved in your suggestion, I tested in SqlCe4 and SQL Server 2008. The generated query is "SELECT count(* ) as y0_ FROM [People] this_" in both cases. – Talbert

Answer 3

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...

Raincoat

Unfortunately that does not produce the desired SQL - the entire list of records (which can be enormous) is loaded into .NET code before it is counted. – Talbert