SQL doesn't join necessary tables in HotChocolate pagination

Question

I have the following entity Space that has a collection of SpaceMessage

public class Space
{
    public int Id { get; set; }

    [UsePaging(IncludeTotalCount = true, MaxPageSize = 50)]
    public ICollection<SpaceMessage> Messages { get; set; } = new HashSet<SpaceMessage>();
}

The query is defined as

    [UseFirstOrDefault]
    [UseProjection]
    [UseFiltering]
    public IQueryable<Space> GetSpace(ApplicationDbContext dbContext, int id)
    {
        return dbContext.Spaces.Where(e => e.Id == id);
    }

Now I want to query messages of space 1 with pagination.

query {
    space(id: 1) {
        messages(first: 1) {
            edges {
                node {
                    id
                }
            }
        }
    }
}

But returns an empty collection

{
  "data": {
    "space": {
      "messages": {
        "edges": []
      }
    }
  }
}

It's because the SQL doesn't join the massage table

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (43ms) [Parameters=[@__id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT 1
      FROM [Spaces] AS [s]
      WHERE [s].[Id] = @__id_0

If there is any workaround of this it is highly appreciated.

HotChocolate 13.3.1, .NET 7 – Haohan Yang Jul 08 '23 at 10:13 — Haohan Yang, Jul 08 '23 at 10:13

score 1 · Accepted Answer · answered Jul 08 '23 at 19:07

Right now paging is not supported for the navigation properties, and HotChocolate has the bug about it

The only workaround is to define a resolver instead of navigation property:

public class Space
{
    [IsProjected]
    public int Id { get; set; }

    [UsePaging(IncludeTotalCount = true, MaxPageSize = 50)]
    public IQueryable<SpaceMessage> GetSpaceMessages(ApplicationDbContext dbContext)
    {
        return dbContext.SpaceMessages.Where(e => e.SpaceId == Id);
    }

}

SQL doesn't join necessary tables in HotChocolate pagination

1 Answers1