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?

Simple, just add to end : }).Select(x => new { groups = x, count = x.Count()); — jdweng
– jdweng, Commented Mar 3, 2017 at 18:01

Frédéric · Accepted Answer · 2017-03-04 19:17:49Z

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.

ManuelCh · Accepted Answer · 2022-07-12 09:08:00Z

0

I found a workaround:

return data.GroupBy(x => new { x.Item.Parent.Name, x.Date }).Select(x => x.Key).Distinct().Count();

answered Jul 12, 2022 at 9:08

ManuelCh

314 bronze badges

Collectives™ on Stack Overflow

NHibernate LINQ count number of groups in query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related