NHibernate LINQ count number of groups in query

Question

I'm running a report on a table, and grouping by two columns. I want to get the total count of groups in the result, so that I can page the report. However the .Count() method is returning the number of rows in the first group. The query is

return data.GroupBy(x => new { x.Item.Parent.Name, x.Date })
           .Select(x => new Item
           {
                Parent = x.Key.Name,
                Date = x.Key.Date,
                PredictedSales = x.Sum(y => y.PredictedSales),
                RealSales = x.Sum(y => y.ActualSales),
            });

.Count() is performing the following query

select cast(count(*) as INT) as col_0_0_
from   dbo.[table1] table1
   left outer join dbo.[Table2] table2
     on table1.Table2Id = table2.Id
        and 0 /* @p0 */ = table2.Deleted
   left outer join dbo.[Table3] table3
     on table2.Table3Id = table3.Id
where  table1.Date >= '2017-03-01T00:00:00' /* @p2 */
   and table1.Date <= '2017-03-15T00:00:00' /* @p3 */
   and (table1.VariancePercentage is not null)
   and abs(table1.VariancePercentage * 100 /* @p4 */) <= 5 /* @p5 */
group  by table3.Name,
      table1.Date

Whereas what I want is something like select TOP(1) COUNT(*) OVER () FROM.

Is there any way to make this happen using a linq query?

Answer 1

This is a known issue, NH-3154.

Your case requires to count from a sub-query. As far as I know, hql does not support it (subqueries are supported only in select expression or where conditions), so it will likely not be supported soon. (linq-to-nhibernate translates to hql.)

So you have to either use a sql native query for counting (session.CreateSQLQuery("...")), or maybe compromise a bit on functionality and performances:

• Choose a maximum number of pages available to browse.
• Issue following query:

var maxResults = maxPageCount * pageSize + 1;
var count = data.GroupBy(x => new { x.Item.Parent.Name, x.Date })
    .Select(x => x.Key)
    .Take(maxResults)
    .ToList()
    .Count();

• If count == maxResults, you may tell your user there are more pages.

Of course, this will issue a query loading all the grouping keys then counting them in memory. If you must support an high number of pages, this would perform poorly.