Hotchocolate pagination and projection when mapping to a different type than the entity itself

Question

I have the situation where my query does not directly return the the entity type, it is being mapped to another type by a mapping method. I am using the [UsePaging] attribute and I notice that the SQL resulting from the query does not include a 'TOP N' clause and additionally the projection is not applied. If the query returns the entity directly without modification it does have the 'TOP N' clause and projection.

I assume this because of deferred execution.

My question is, in this scenario when I am returning a mapped type, how would I configure things so that paging and projection works as expected, that is, the query to the database is for a page worth of rows and only the required columns, not the whole table and all columns?

Below is a simple example. I have provided two methods in the query, one returning entity directly and the other using a mapping method; for simplicity the mapping method just creates a new collection of the same type.

public class Query {

    [UsePaging(IncludeTotalCount = true, DefaultPageSize = 10, MaxPageSize = int.MaxValue - 1)]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IEnumerable<WorksView>? GetWorks([Service] AIDataContext context)
    {
        return context.WorksViews;
    }

    [UsePaging(IncludeTotalCount = true, DefaultPageSize = 10, MaxPageSize = int.MaxValue - 1)]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IEnumerable<WorksView>? GetWorksMapped([Service] AIDataContext context)
    {
        return Map(context.WorksViews);
    }


    private static IEnumerable<WorksView> Map(IEnumerable<WorksView> items)
    {
        
        IEnumerable<WorksView> worksViews = new List<WorksView>();

        foreach (var item in items)
        {
            ((List<WorksView>)worksViews).Add(item);
        }

        return worksViews;
    }
}

The query to the unmapped method

{
  works (first:25, where: {region:{contains:"|PLPUBS|"}} , order:[{worksiteId:ASC}]) {
    pageInfo {
      hasNextPage,
      hasPreviousPage,
      startCursor,
      endCursor
    }
    nodes{
      id
      region
    }
  }
}

produces this SQL

Executed DbCommand (23ms) [Parameters=[@__p_1='?' (DbType = Int32), @__p_0='?' (Size = 50) (DbType = AnsiString)], CommandType='Text', CommandTimeout='120']
      **SELECT TOP(@__p_1)** [w].[Id], [w].[Region]
      FROM [Works] AS [w]
      WHERE (@__p_0 LIKE '') OR (CHARINDEX(@__p_0, [w].[Region]) > 0)
      ORDER BY [w].[WorksiteId]

The query to the mapped version

{
  worksMapped (first:25, where: {region:{contains:"|PLPUBS|"}} ) {
    pageInfo {
      hasNextPage,
      hasPreviousPage,
      startCursor,
      endCursor
    }
    nodes{
      id
      region
    }
  }
}

produces this SQL

Executed DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='120']
      SELECT [w].[WorksiteId], [w].[AdditionalInformation], [w].[AffectedLines], [w].[BusinessWorksiteRef], [w].[CostProvider], [w].[CyclicParent], [w].[DeliveryGroup], [w].[DistanceFromChains], [w].[DistanceFromMiles], [w].[DistanceToChains], [w].[DistanceToMiles], [w].[ElrFrom], [w].[ElrTo], [w].[EsGroupCode], [w].[EsPrimarySupplementary], [w].[EstimatedWorkHours], [w].[FromDateTime], [w].[GeneralPlanningRemarks], [w].[GeneralRemarks], [w].[GeoLine], [w].[HasIsolation], [w].[Id], [w].[JobBankId], [w].[LocationFrom], [w].[LocationTo], [w].[LorFrom], [w].[LorTo], [w].[NrContact], [w].[PossessionId], [w].[ProjectId], [w].[Region], [w].[Requestor], [w].[TaskLineNumber], [w].[ToDateTime], [w].[TrafficRemarks], [w].[WorkCategory], [w].[WorkType], [w].[WorksitePriority], [w].[WorksiteStatus]
      FROM [Works] AS [w]

Which is not paging and has no projection.

Hotchocolate pagination and projection when mapping to a different type than the entity itself

0 Answers0