NHibernate QueryOver <> - Aggregate function over SubQuery

Question

How to 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 an internal query that still works:

 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 the number of rows from it. It can be done?

Unfortunately, my RDBMS dialect (MsSqlCe40Dialect) does not support COUNT DISTINCT, so I have no advantage of using SelectCountDistinct ().

Answer 1

Well, I don’t know the reasons for 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 this helps ...

Answer 2

I am not familiar with QueryOver, but I used the following aggregate function when a subquery was not possible for this type of counting, it was thought that this could be useful, and at the time of publication I found several problems that I did not know about before, so I sent them also.

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

Cumulative method

 SELECT COUNT(DISTINCT FirstName+LastName ) FROM People 

Special Occasion Accommodation

similar names for the combinations "Joe Smith" and "Jos Meath" (assuming ~ is not in your dataset)

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

nullifies (assuming ^ is not in your dataset)

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

Finishing the gap, it seems that RTRIM is an integral part of the group

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

Benchmarking (80 thousand lines of data on a quad-core AMD processor)

80-100ms - run the Sub Query method (see OP)

800-1200 ms - the aggregate method with a clear one, adapted for special cases, does not seem to have a noticeable difference.

Answer 3

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

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