Adding a projection to an NHibernate criteria stops it from performing default entity selection

Question

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.

Goldschmidt

Answer 1

I think it is not possible in Criteria, it has some limits.

You could get the id and load items in a subsequent query:

var query = Session
    .CreateCriteria<Item>("item")
    .SetProjection(Projections.ProjectionList()
       .Add(Projections.SqlFunction("rowcount", NHibernateUtil.Int32))
       .Add(Projections.Id()));

If you don't like it, use HQL, you can set the maximal number of results there too:

IList<Item> result = Session
    .CreateQuery("select item, rowcount() from item where ..." )
    .SetMaxResult(100)
    .List<Item>();

Sanctus

Answer 2

Use CreateMultiCriteria.

You can execute 2 simple statements with only one hit to the DB that way.

Tedda

Using CreateMultiCriteria would result in two separate SQL queries to be generated. Although they'll be executed in a single batch, it still wouldn't be as efficient as executing just a single query. I want 'SELECT , COUNT() OVER() AS rowcount FROM Items', not 'SELECT * FROM Items; SELECT COUNT(*) AS rowcount FROM Items' as the CreateMultiCriteria scenario would get me. – Libbylibeccio

Answer 3

I am wondering why using Criteria is a requirement. Can't you use session.CreateSQLQuery? If you really must do it in one query, I would have suggested pulling back the Item objects and the count, like:

select {item.*}, count(*) over() 
from Item {item}

...this way you can get back Item objects from your query, along with the count. If you experience a problem with Hibernate's caching, you can also configure the query spaces (entity/table caches) associated with a native query so that stale query cache entries will be cleared automatically.

Individuate

Thanks for your suggestion but I really want to do it using Criteria because that way I can easily apply paging to many of my existing Criteria by simply expanding them using an extension method 'List(start, limit, out totalRowCount)'. Besides, using literal SQL (Server) queries would make my solution less platform agnostic while the current solution which uses a customized dialect, would probably be a lot easier to port to a different DBMS. – Libbylibeccio

Answer 4

If I understand your question properly, I have a solution. I struggled quite a bit with this same problem.

Let me quickly describe the problem I had, to make sure we're on the same page. My problem came down to paging. I want to display 10 records in the UI, but I also want to know the total number of records that matched the filter criteria. I wanted to accomplish this using the NH criteria API, but when adding a projection for row count, my query no longer worked, and I wouldn't get any results (I don't remember the specific error, but it sounds like what you're getting).

Here's my solution (copy & paste from my current production code). Note that "SessionError" is the name of the business entity I'm retrieving paged data for, according to 3 filter criterion: IsDev, IsRead, and IsResolved.

ICriteria crit = CurrentSession.CreateCriteria(typeof (SessionError))
    .Add(Restrictions.Eq("WebApp", this));

if (isDev.HasValue)
    crit.Add(Restrictions.Eq("IsDev", isDev.Value));

if (isRead.HasValue)
    crit.Add(Restrictions.Eq("IsRead", isRead.Value));

if (isResolved.HasValue)
    crit.Add(Restrictions.Eq("IsResolved", isResolved.Value));

// Order by most recent
crit.AddOrder(Order.Desc("DateCreated"));

// Copy the ICriteria query to get a row count as well
ICriteria critCount = CriteriaTransformer.Clone(crit)
    .SetProjection(Projections.RowCountInt64());
critCount.Orders.Clear();

// NOW add the paging vars to the original query
crit = crit
    .SetMaxResults(pageSize)
    .SetFirstResult(pageNum_oneBased * pageSize);

// Set up a multi criteria to get your data in a single trip to the database
IMultiCriteria multCrit = CurrentSession.CreateMultiCriteria()
    .Add(crit)
    .Add(critCount);

// Get the results
IList results = multCrit.List();

List<SessionError> sessionErrors = new List<SessionError>();
foreach (SessionError sessErr in ((IList)results[0]))
    sessionErrors.Add(sessErr);

numResults = (long)((IList)results[1])[0];

So I create my base criteria, with optional restrictions. Then I CLONE it, and add a row count projection to the CLONED criteria. Note that I clone it before I add the paging restrictions. Then I set up an IMultiCriteria to contain the original and cloned ICriteria objects, and use the IMultiCriteria to execute both of them. Now I have my paged data from the original ICriteria (and I only dragged the data I need across the wire), and also a raw count of how many actual records matched my criteria (useful for display or creating paging links, or whatever). This strategy has worked well for me. I hope this is helpful.

Flexure

Note that I'm not using a RowCount projection because that would only count the actually selected rows and I'm not interested in that. COUNT(*) OVER counts all rows however, mitigating the need to separately retrieve that. Still, although they are batched, you are executing two queries, which ofcourse is potentially less efficient than executing just one. Your solution is fine on its own but it doesn't quite meet my requirement of executing a single SQL query only. OK, it's not a real requirement but I don't want to give up yet on the opportunity to optimize here. – Libbylibeccio

Answer 5

I would suggest investigating custom result transformer by calling SetResultTransformer() on your session.

Syncarpous

Answer 6

Create a formula property in the class mapping:

<property name="TotalRecords" formula="count(*) over()" type="Int32" not-null="true"/>;

IList<...> result = criteria.SetFirstResult(skip).SetMaxResults(take).List<...>();
totalRecords = (result != null && result.Count > 0) ? result[0].TotalRecords : 0;
return result;

Adames

The total records is not a property of the entity. Adding this mapping would likely result in caching issues. See also: stackoverflow.com/questions/1627707 – Buckeen

Thanks for the heads up! In my case, I did not have the query cache enabled and this was provided through a stateless Web Service layer, hence the session was never shared. – Immigration