Keyset Pagination Fails with Nested Collections: ArgumentException in Sorting by Nested Property

[vamsi200989]

I am encountering an issue with keyset pagination using the MR.EntityFrameworkCore.KeysetPagination library when dealing with nested collections. Specifically, I am trying to paginate PaymentRequest entities sorted by RemittanceDetail.Number, but I encounter an exception.

public class User
{
    public Guid UserId { get; set; }
    public List<Address> Addresses { get; set; }
}

public class Address
{
    public Guid AddressId { get; set; }
    public string City { get; set; }
    public string Street { get; set; }
}

1. Set Up the Keyset Query:

   var keysetQuery = KeysetQuery.Build<User>(builder =>
   {
   builder.Ascending(u => u.Addresses.First().City);
   });

2. Sample Code

var searchQuery = _dbContext.Users.Include(u => u.Addresses).AsQueryable();
            var paginationContext = searchQuery.KeysetPaginate(keysetQuery, KeysetPaginationDirection.Forward, reference);
            var users = await paginationContext.Query
                .Take(10)
                .Select(u => new 
                {
                    UserId = u.UserId,
                    Addresses = u.Addresses.Select(a => new { City = a.City, Street = a.Street }).ToList()
                })
                .ToListAsync(cancellationToken);

            paginationContext.EnsureCorrectOrder(users);
            var hasNext = await paginationContext.HasNextAsync(users);

Failure :

This fails in HasNextAsync method with the following exception :

**System.ArgumentException: Expression of type 'System.Collections.Generic.List1[<>f__AnonymousType61[System.String]]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[Namespace.NestedEntityType]' of method 'Namespace.NestedEntityType FirstNestedEntityType'**`

The example provided in the Readme for Loose Typing is simple, it just have a navigation property but in our case we have a collection and there is no example on how to deal with nested collections.

Could you please provide an example on how to sort with properties in the nested collection ?

[mrahhal]

Using collections in the keyset is not supported. Even if it was, it defeats the goal of using keyset pagination as it's adding a join in the base condition of the query that can't be satisfied with an index, leading to a table scan.

A solution is to create a computed column that stores Addresses.First().City and add this column to the keyset (an example of this is shown in caveats.md). This way you can index this column and you can use it in the keyset as any other column.

[mrahhal]

I'm closing this, please reopen if you still have issues.