Entity collection and Typed List/Typed View paging

Paging is the way to browse through a list of objects or rows of data one page at a time. This can be handy when you have thousands of rows / objects matching search criteria but you want to enlist only a small number at once. When executing a query using the DataAccessAdapter class methods, you can specify which page to retrieve for typed lists, typed views or entity collections, instead of getting all the results at once.

Queries using Linq to LLBLGen or QuerySpec are using paging constructs of those systems and are described in their own sections: Paging in Linq and Paging in QuerySpec. This section describes the various options available to you when fetching elements using the Low-level API.

Client side paging vs. server side paging

Paging in general happens on the server, however the LLBLGen Pro runtime framework switches to client-side paging at the datareader level if the paging query can't be generated without causing errors on the server. This happens if a DISTINCT violating construct is present in the SQL query, e.g. a type that's not to be used with DISTINCT or an order by on a field that's not in the projection. The datareader-level paging is efficient (it discards any rows before the page to read and stops reading data once the page to read is fully read) however can be slower than a server-side paging query.

To determine whether the framework switched to client-side paging / distinct filtering / row limiting, it will append to the SQL query output to the ORMQueryExecution tracer a string which signals this: "Requires client side paging", "Requires client side distinct filtering" and "Requires client side limiting", if the ORMQueryExecution tracer is set to level 4 (verbose). See ORM Support classes tracing for details on this tracer.

Paging using an entity collection fetch

Paging through an entity collection is implemented in an overload of DataAccessAdapter.FetchEntityCollection(). The particular overload accepts the page size, which is the number of objects to retrieve by the fetch action, and the page number to retrieve. If you for example pass 10 for the page size and 4 for the page number, you'll get record number 31-40, the first record is 1, the first page is also numbered 1. Paging is disabled if you pass 0 for the page number or 0 or 1 for the page size.

Get the total number of objects

When using paging, it's often required to know how many pages a given resultset contains. For example, you want to show a list of page numbers the user can choose from, like Google uses. You can retrieve the number of objects matching your filter by using the DataAccessAdapter's GetDbCount() method. The method below uses an Aggregate function. The example retrieves the number of different order objects of customers from "France".

var filter = new RelationPredicateBucket(CustomerFields.Country.Equal("France"));
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
int amount = 0;
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    amount = (int)adapter.GetDbCount(new OrderEntityFactory().CreateFields(), filter, null, false);
}

The value in amount can now be used to calculate the total number of pages when the page size is given: total number of pages = (total number of objects / pagesize) + n, where n is either 0 (total number of objects modulo pagesize is 0) or 1 (total number of objects modulo pagesize > 0). Below is the code to retrieve page 4, with a pagesize of 10 objects. We re-use the filter objects used in the GetScalar() call:

var orders = new EntityCollection<OrderEntity>();
adapter.FetchEntityCollection(orders, filter, 0, null, 4, 10);

After this call, orders contains 10 objects, which formed the 4th page in the result set matching the filter defined. No sorting is applied here, but if you specify a sort expression, the sorting is performed prior to the paging logic.

It's recommended to use a sorter in your query if you use paging to be sure the data is ordered in a predictable fashion. SQL by definition applies no ordering on SELECT resultsets.

Paging using a TypedList or TypedView fetch

The paging functionality is also available for typed list and typed view classes, through an overload of the FetchTypedList() method for typed lists and an overload of FetchTypedView for typed views. For typed lists and typed views, the same definitions are valid as for collections: page numbers start at 1, the first record is numbered 1 and paging is disabled if you pass in a page number of 0 or you pass in a page size of 0 or 1.

Get the total number of rows

Getting the total number of rows for a typed list, is a bit different than it is for a collection. Instead of creating a new RelationPredicateBucket object, you use the RelationPredicateBucket returned from the typed list's method GetRelationInfo().

To that object, you add your predicates. For typed views, it works the same as enlisted above for entities. Below is the code to get the number of rows in a typed list with order-customer rows.

var orderCustomer = new OrderCustomerTypedList();
var filter = orderCustomer.GetRelationInfo();
filter.PredicateExpression.Add(CustomerFields.Country == "France");
int amount = 0;
using(var adapter = new DataAccessAdapter())
{
    amount = (int)adapter.GetDbCount(orderCustomer.GetFieldsInfo(), filter, null, false);
}

Fetching a given page in the typed list or typed view is then boiling down to using the FetchTypedList() overload which accepts the two paging parameters for fetching a typed list' page or calling the right FetchTypedView() method for fetching the required page for a typed view. Be sure to clear the typed list/typed view object before calling the fetch methods again to fetch another page.