R – SubSonic and Paging using ROW_NUMBER ()

This is one case with SubSonic where I think it is easier to create a stored procedure and build the collection from the result. Paging in a sproc isn't that difficult to implement (capture the sql generated by SubSonic and reuse it).

You can build a typed collection from the sproc by passing SPs.SPNameHere.GetReader() to the ItemCollection.Load() method. Make sure the sproc returns what a SELECT * FROM Item would return.

You'll need to add paging to your stored procedure. Methods to accomplish this vary based on which database you're using. I would capture the SQL generated by a paged query, copy that SQL into a new sproc, add params for page number and page size, then access the Stored procedure proxy method for your newly-created sproc.