CODEWITHSUNDEEP
c#linqnhibernatehql
Bonnotbh
Bonnotbh

Reputation: 545

NHibernate LINQ count number of groups in query

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?

Upvotes: 1

Views: 1146

Answers (2)

ManuelCh
ManuelCh

Reputation: 31

I found a workaround:

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

Upvotes: 0

Fr&#233;d&#233;ric
Fr&#233;d&#233;ric

Reputation: 9864

This is a known issue, NH-3154.

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

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.

Upvotes: 3

Related Questions