In .NET Hot Chocolate GraphQL is it possible to apply Custom Pagination while still using [UseProjec-CodePudding

Though [UseProjection] middleware works out of the box, the default UsePaging seems to still apply Offset based selection in the backend SQLServer Query. I want to use the [UseProjection] provided by HotChocolate, but want to have customized cursor based pagination.

Initially I planned to use Dapper as the micro-OR/M with my own custom pagination logic implemented in Resolver and my custom SQL Query. However the problem with the above approach is that I am not able to leverage the advantages provided by the default [UseProjections] of HotChocolate. So I am fully using HotChocolate with Entity Framework Core. With this standard approach my Projections work as expected. But now I am not able to apply custom pagination. The back-end SQL Server Query in the Profiler shows "OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY" which is still an Offset query really.

My Query class:

namespace Efct2.GraphQL.Query
{
    [ExtendObjectType(typeof(Query))]
    public class StyleQuery
    {
        [UsePaging]
        [UseProjection]
        public async Task<IQueryable<Style>> Styles([Service] IStyleService styleService)
        {
            return await styleService.GetAsync();
        }
    }
}

The StylService and StyleRepository have an implementation similar to the one given in HotChocolate Projections related question

So I am not attaching the code for those here. However I can add it if required.

My GraphQL input query is as below:

styles (first: 10, after: "OQ==") {
    pageInfo {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
    nodes {
      companyCode
      divisionCode
      itemNumber
      colorCode
      description
      categoryCode
      classCode
    }
  }

}

Given below is the SQLServer SQL from the backend profiler:

exec sp_executesql N'SELECT [s].[Company_Code] AS [CompanyCode], [s].[Division_Code] AS [DivisionCode], [s].[Item_Number] AS [ItemNumber], [s].[Color_Code] AS [ColorCode], [s].[Description], [s].[Category_Code] AS [CategoryCode], [s].[Class_Code] AS [ClassCode]
FROM [Style] AS [s]
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',
    N'@__p_0 int,@__p_1 int',@__p_0=10,@__p_1=11

Would really appreciate help on the above.

CodePudding user response：

Yes, In Hot Chocolate the paging is abstracted through the IPagingHandler and the IPagingProvider.

https://github.com/ChilliCream/hotchocolate/blob/main/src/HotChocolate/Core/src/Types.CursorPagination/CursorPagingProvider.cs https://github.com/ChilliCream/hotchocolate/blob/main/src/HotChocolate/Core/src/Types.CursorPagination/CursorPagingHandler.cs