A short while back a colleague asked a good question: How would you reproduce a "SELECT TOP 5 * FROM [Table]" in his ActiveRecord-based project.
Enter the "SlicedFind" static methods, one of them defined as follows (there are 7 overloads):
public static T[] SlicedFindAll(int firstResult, int maxResults, params NHibernate.Expression.ICriterion[] criteria)
For my examples, I'll make use of a fictional class Foo (mapped to a table) defined as follows:
[ActiveRecord]
public class Foo :ActiveRecordBase<Foo>
{
[PrimaryKey]
public int FooId { get; set; }
[Property]
public string Title { get; set; }
[Property]
public string TotalAmount { get; set; }
[Property]
public bool IsDeleted { get; set; }
}
Using the SlicedFind to retrieve the top 5 records is then as easy as this:
Foo[] foos = Foo.SlicedFindAll(0, 5, new ICriterion[] { Expression.Eq("IsDeleted", false) });
The third parameter actually allows you to filter the records on the server first before the results are returned.
Another very good use of the SlicedFind is for paging, e.g. when you have a grid only showing 5 records at a time, and you want the second set of 5.
Foo[] foos = Foo.SlicedFindAll(5, 5, new ICriterion[] { Expression.Eq("IsDeleted", false) });
On another note, I was doing a SQL trace to see what type of SQL was being generated, and I was suprised to see this statement for the "paging"
WITH query AS
(
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS
__hibernate_row_nr__, this_.FooId as FooId0_0_, this_.Title as Title0_0_, this_.TotalAmount as TotalAmo3_0_0_, this_.IsDeleted as IsDeleted0_0_
FROM Foo this_
)
SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY __hibernate_row_nr__
This means that 10 rows are being returned - the start position (5) plus the number of rows (5) and the ActiveRecord/NHibernate is discarding the excess rows. I would ideally like to see it as follows:
WITH query AS
(
SELECT ROW_NUMBER() OVER (ORDER BY FooId) AS __hibernate_row_nr__,
this_.FooId as FooId0_0_, this_.Title as Title0_0_, this_.TotalAmount as TotalAmo3_0_0_, this_.IsDeleted as IsDeleted0_0_
FROM Foo this_
)
SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN 5 AND 10
I'll be having a look at the source code of ActiveRecord/NHibernate to see if these mods can be made, and will post my findings.