I'm writing an NHibernate criteria that selects data supporting
paging. I'm using the COUNT(*) OVER() expression from SQL Server 2005
(+) to get hold of the total number of available rows, as suggested by
Ayende Rahien. I need that number to be able to calculate how many
pages there are in total. The beauty of this solution is that I don't
need to execute a second query to get hold of the row count.
However, I can't seem to manage to write a working criteria (Ayende
only provides an HQL query).
Here's an SQL query that shows what I want and it works just fine.
Note that I intentionally left out the actual paging logic to focus on
the problem:
SELECT Items.*, COUNT(*) OVER() AS rowcount
FROM Items
Here's the HQL:
select
item, rowcount()
from
Item item
Note that the rowcount() function is registered in a custom NHibernate
dialect and resolves to COUNT(*) OVER() in SQL.
A requirement is that the query is expressed using a criteria.
Unfortunately, I don't know how to get it right:
var query = Session
.CreateCriteria<Item>("item")
.SetProjection(
Projections.SqlFunction("rowcount", NHibernateUtil.Int32));
Whenever I add a projection, NHibernate doesn't select item (like it
would without a projection), just the rowcount() while I really need
both. Also, I can't seem to project item as a whole, only it's
properties and I really don't want to list all of them.
I hope someone has a solution to this. Thanks anyway.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
