Search code examples
c#.netpaginationnhibernate

NHibernate - How do I use a sum project on paged results

I'm trying to use paging in conjunction with a sum projection to get a sum of the values in a column for just the page of results I'm interested in. I'm using .NET, C# and NHibernate 3.1

I have an ICriteria to start with which is related to all rows from the associated db table.

I'm then doing the following to get a version with the first page (say, 10 items out of 40):

ICriteria recordsCriteria = CriteriaTransformer.Clone(criteria);
recordsCriteria.SetFirstResult(0);
recordsCriteria.SetMaxResults(10);

I'm using this ICriteria for something else so I then create two further clones:

ICriteria totalAggCriteria = CriteriaTransformer.Clone(criteria);
ICriteria pageAggCriteria = CriteriaTransformer.Clone(recordsCriteria);

If I take a look inside these two new ones the first has 40 items in and the second has 10 - exactly what I want.

Let's say the objects coming back from the DB have a column called "ColA" and it's of type Int32.

From this, I want the sum of all 40 ColA values and the sum of the first 10 ColA values.

To get the sum of all 40 ColA values, I do the following:

totalAggCriteria.SetProjection(NHibernate.Criterion.Projections.Sum("ColA"));
var totalSum = totalAggCriteria.UniqueResult();

The value in totalSum is correct.

To get the sum of the first 10 ColA values, I'm trying the following:

pageAggCriteria.SetProjection(NHibernate.Criterion.Projections.Sum("ColA"));
vat pageSum = pageAddCriteria.UniqueResult();

However, this gives me the same value as the previous one - for all 40 ColA values.

I've also tried the following but it gives the same result:

pageAggCriteria.SetProjection(NHibernate.Criterion.Projections.Sum(column));
pageAggCriteria.SetFirstResult(firstResult.Value);
pageAggCriteria.SetMaxResults(pageSize.Value);
pageSum = pageAggCriteria.UniqueResult();

And also:

pageAggCriteria.SetFirstResult(firstResult.Value);
pageAggCriteria.SetMaxResults(pageSize.Value);
pageAggCriteria.SetProjection(NHibernate.Criterion.Projections.Sum(column));  
pageSum = pageAggCriteria.UniqueResult();

Can anyone give an idea on where I'm going wrong and how I can actually get the sum of the ColA values in the first 10 results?

Thanks

Solution

  • Probably easiest to do that sum client side. The aggregate function is operating on the whole table. What you are trying to do is run the aggregate function against the paged result which I don't think is possible with NH.

    In other words, you want select sum(colA) from (select top 10 ...) but that criteria will give you select top 10 sum(colA) from ...)