Generated SQL query is correct, but no related entities are returned in GraphQL

[StefH]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the bug

Generated SQL query is correct, but no related entities are returned in GraphQL

Steps to reproduce

Classes:

public class Student
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [UseFiltering]
    public int Id { get; set; }
    public string LastName { get; set; }
    public string FirstMidName { get; set; }
    public DateTime EnrollmentDate { get; set; }

    public virtual ICollection<Enrollment> Enrollments { get; set; }
}

Query

[UseDbContext(typeof(SchoolContext))]
[UseProjection]
[UseFiltering]
[UseSorting]
public IQueryable<Student> GetStudents([ScopedService] SchoolContext dbContext)
{
    return dbContext.Students.Include(s => s.Enrollments).AsNoTracking();
}

The generated SQL looks like this:

SELECT "s"."EnrollmentDate", "s"."FirstMidName", "s"."Id", "s"."LastName", "e"."CourseId", "e"."EnrollmentId", "e"."StudentId"
      FROM "Students" AS "s"
      LEFT JOIN "Enrollments" AS "e" ON "s"."Id" = "e"."StudentId"
      WHERE "s"."Id" = @__studentId_0
      ORDER BY "s"."Id", "e"."EnrollmentId"

But the output shows no enrollments:

{
  "data": {
    "students": [
      {
        "id": 1,
        "lastName": "Foo",
        "enrollments": []
      },
      {
        "id": 2,
        "lastName": "Bar",
        "enrollments": []
      },
      {
        "id": 3,
        "lastName": "Baz",
        "enrollments": []
      }
    ]
  }
}

The project can be found here: https://github.com/StefH/HotChocolateExample/tree/main/ContosoUni

Relevant log output

[13:11:23 DBG] dbug: 8/25/2021 13:11:23.483 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression: 
      'queryContext => new SingleQueryingEnumerable<StudentModel>(
          (RelationalQueryContext)queryContext, 
          RelationalCommandCache.SelectExpression(
              Projection Mapping:
              SELECT s.EnrollmentDate, s.FirstMidName, s.Id, s.LastName, e.CourseId, e.EnrollmentId, e.StudentId
              FROM Students AS s
              LEFT JOIN Enrollments AS e ON s.Id == e.StudentId
              WHERE s.Id == @__studentId_0
              ORDER BY s.Id ASC, e.EnrollmentId ASC), 
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, StudentModel>, 
          ContosoUniversity.SchoolContext, 
          False, 
          True
      )'
[13:11:23 DBG] dbug: 8/25/2021 13:11:23.510 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command) 
      Creating DbCommand for 'ExecuteReader'.
[13:11:23 DBG] dbug: 8/25/2021 13:11:23.510 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
[13:11:23 DBG] dbug: 8/25/2021 13:11:23.512 RelationalEventId.ConnectionOpening[20000] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opening connection to database 'main' on server 'uni.db'.
[13:11:23 DBG] dbug: 8/25/2021 13:11:23.513 RelationalEventId.ConnectionOpened[20001] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opened connection to database 'main' on server 'C:\Users\azurestef\Documents\Github\HotChocolateExample\ContosoUni\uni.db'.
[13:11:23 DBG] dbug: 8/25/2021 13:11:23.515 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@__studentId_0='1' (DbType = String)], CommandType='Text', CommandTimeout='30']
      SELECT "s"."EnrollmentDate", "s"."FirstMidName", "s"."Id", "s"."LastName", "e"."CourseId", "e"."EnrollmentId", "e"."StudentId"
      FROM "Students" AS "s"
      LEFT JOIN "Enrollments" AS "e" ON "s"."Id" = "e"."StudentId"
      WHERE "s"."Id" = @__studentId_0
      ORDER BY "s"."Id", "e"."EnrollmentId"
[13:11:23 DBG] info: 8/25/2021 13:11:23.522 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (9ms) [Parameters=[@__studentId_0='1' (DbType = String)], CommandType='Text', CommandTimeout='30']
      SELECT "s"."EnrollmentDate", "s"."FirstMidName", "s"."Id", "s"."LastName", "e"."CourseId", "e"."EnrollmentId", "e"."StudentId"
      FROM "Students" AS "s"
      LEFT JOIN "Enrollments" AS "e" ON "s"."Id" = "e"."StudentId"
      WHERE "s"."Id" = @__studentId_0
      ORDER BY "s"."Id", "e"."EnrollmentId"

Additional Context?

Using SQLite + EF Core 5.0.9

Product

Hot Chocolate

Version

12.0.0-preview-32

[PascalSenn]

@StefH is the project you provided the code you run? because there is a major difference in the student model

[StefH]

@PascalSenn Sorry, I forgot a commit. Please review the project now.

[StefH]

@PascalSenn Note that when I use

.UseInMemoryDatabase("uni")

Then it works:

{
  "data": {
    "students": [
      {
        "id": 1,
        "lastName": "Foo",
        "enrollments": [
          {
            "enrollmentId": 1,
            "courseId": 1
          }
        ]
      },
      {
        "id": 2,
        "lastName": "Bar",
        "enrollments": [
          {
            "enrollmentId": 2,
            "courseId": 1
          }
        ]
      },
      {
        "id": 3,
        "lastName": "Baz",
        "enrollments": [
          {
            "enrollmentId": 3,
            "courseId": 1
          }
        ]
      }
    ]
  }
}

[AcidNoX]

We are also having this issue with EntityFrameworkCore 6.0.3 and Hotchocolate packages 12.6.0.

Is there any update on when this is going to be looked at? Or maybe it already has in v13?

EDIT: We are using postgres SQL but can confirm that the SQL also looks correct but the projected properties aren't returned in the response. Something to note is that this is only an issue when skip > 2 and ordering by anything

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[StefH]

Any update?

[PascalSenn]

@StefH Looking at it my best guess would be it's an automapper issue. EF core does not like more than one Select(...) on the entites. Does this problem also occure when you do not map the entities?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[StefH]

When I have time, I'll try to use latest versions and try to reproduce.