Other solution is to implement pagination at the logic level by using LINQ like in the next example:
public IQueryable<T> LoadGridData<T>(IQueryable<T> dataSource, int pageSize, int pageIndex, out int count)
{
var query = dataSource;
count = query.Count();
if (pageIndex < 1)
pageIndex = 1;
var data = query.Skip((pageIndex - 1) * pageSize).Take(this.pageSize);
return data;
}
So you could build LINQ for getting your data, then use a method like the one above to run the LINQ and to get the data paginated, see the example below:
IQueryable<Order> ordersQuery = from o in dataContext.Orders
from oi in dataContext.OrderItems
where o.OrderItems.Count > 1 && oi.OrderID == o.ID && oi.WarehouseArticleID == this.ID
orderby o.ID descending
select o;
int count;
IQueryable<Order> data = LoadGridData<Order>(ordersQuery , pageSize, pageIndex, out count);