How to implement pagination with group by using Entity Framework Core 3.0 without retrieving all rows?

Question

How would you implement pagination when the input data needs to be grouped first? I understand how to implement pagination from the link below:

LINQ and pagination

, but I would like to be able to do this where each item in the paginated list is a group (that can be expanded) from the input data. Something similar to the code below - to prevent retrieving all rows of the table into memory, ordersList is IQueryable. The IQueryable returned is what I would like to pass into the pagination function.

from order in ordersList
group order by order.FullName into customers
select customers

However, a query like this runs on the client (and actually throws an exception in Entity Framework Core 3.0+). Is there a way to only retrieve the items on the current page for this situation?

Answer 1

You have to retrieve limited data and then group on the client side:

var keys = ordersList
   .Select(o => new {o.FullName})
   .Distinct()
   .OrderBy(о => o.FullName)
   .Skip(pageNumber * pageSize)
   .Take(pageSize);

var items =
   from order in ordersList
   join key in keys on order.FullName equals key.FullName
   select order;

var result = 
   from order in items.AsEnumerable()
   group order by order.FullName into customers
   select customers;

Answer 2

You must paginate by group. You should use the group number instead of the page number.

//group sequence
int groupSeq = 1;

//select current group
var p = (from order in context.TBLGroups
       group order by order.FullName into customers
       select customers.Key).OrderBy(a => a).Skip(groupSeq - 1).Take(1).FirstOrDefault();
       string FullName = p.ToString();

//get all items in current group
var items = (from order in context.TBLGroups
       where order.FullName == FullName
       select order).ToList();