title: How to achieve optimized many to many querying in GraphQL (HotChocolate)
body: <p>Imagining that I have a <strong>N-N</strong> relationship between Student and the Course, .NET structure is typical N-N translated to CLR types <strong>Student</strong>, <strong>StudentCourse</strong> and <strong>Course</strong>.</p>
  <h4>Desired result:</h4>
  <p>What I want to achieve when querying for all students like this ...</p>
  <pre><code>query { 
   allStudents { 
       name,
       courses { 
           name,
           rating
         }
     }
  }
  </code></pre>
  <p>... Is to avoid N+1 graph issues.</p>
  <p>Instead I would like to end end up with the query similar to this one:</p>
  <pre><code>SELECT s.Name
        FROM [Schema].[Student] AS [s]
        LEFT JOIN (
            SELECT course_id, student_id
            FROM [Relationships].[student_course] AS [c]
            INNER JOIN [Schema].[course] AS [c0] ON [c].[course_id] = [c0].[id]
        ) AS [t] ON [s].[id] = [t].[student_id]
        WHERE [m].[id] IN (1, 2, 4, 8, 10, 12) -- All student id's are loaded with data loader?
        ORDER BY [m].[id], [t].[id]
  </code></pre>
  <h4>Current implementation glimpse:</h4>
  <p><em>I created a resolver GetCoursesForStudent for batch fetching course for student.</em></p>
  <pre><code> public async Task&lt;Course&gt; GetCoursesForStudent([Parent] Student student, 
                                                      CoursesForStudentDataLoader dataLoader, 
                                                      IResolverContext context, 
                                                      CancellationToken cancellationToken = default!)
      {
  // This is generating a SELECT for each student, maybe load in a batch somehow?
         var courseIds = await service.GetCourseIdsForStudent(student.Id, cancellationToken);
  
         return await dataLoader.LoadAsync(student.Id, cancellationToken);
      }
  </code></pre>
  <p><em>And the data loader</em></p>
  <pre><code>public class CoursesForStudentDataLoader : BatchDataLoader&lt;int, Course&gt;
      {
          public CoursesForStudentDataLoader(IBatchScheduler batchScheduler) 
              : base(batchScheduler)
          {
          }
  
          protected override async Task&lt;IReadOnlyDictionary&lt;int, Course&gt;&gt; LoadBatchAsync(IReadOnlyList&lt;int&gt; keys, 
  CancellationToken cancellationToken)
          {
              var courses = repo.Queryable()
                             .Where(x =&gt; keys.Contains(x.StudentId))
                          .Select(item =&gt; item.Course);
  
              var fetched = await courses.ToDictionaryAsync(key =&gt; key.Id, cancellationToken);
  
              // fetched contains all the courses!
              return fetched;
          }
      }
  </code></pre>
  <p><em>And object type definition with the resolver for companies.</em></p>
  <pre><code>protected override void Configure(IObjectTypeDescriptor&lt;Student&gt; descriptor)
      {
          descriptor.Field&lt;StudentResolvers&gt;(t =&gt; 
                          t.GetCoursesForStudent(default!, default!, default!, default!)).Name(&quot;courses&quot;)
                              .Type&lt;ListType&lt;NonNullType&lt;CourseObjectType&gt;&gt;&gt;();
      }
  </code></pre>
  <h4>Current results:</h4>
  <p>Currently this generates a sql query for each student to fetch the course id's for each student. (<strong>N+1 issue</strong>)
  And then it uses a data loader to buffer course ids and executes the query to fetch the courses only once. (N+1 avoided, and this looks good).</p>
  <p>So I end up with the following sql:</p>
  <pre><code>  SELECT [c].[course_id]
        FROM [Relationships].[student_course] AS [c]
        WHERE [c].[student_id] = @__studentId_0
    
    SELECT [c].[course_id]
        FROM [Relationships].[student_course] AS [c]
        WHERE [c].[student_id] = @__studentId_0
  
    SELECT [c].[course_id]
        FROM [Relationships].[student_course] AS [c]
        WHERE [c].[student_id] = @__studentId_0
  
  ... - N (clearly undesirable)
  
  
    SELECT [c].[id], [c].[name], [c].[rating]
    FROM [Schema].[course] AS [c]
    WHERE [c].[id] IN (7, 9, 12)
  </code></pre>
  <p>So I wonder what is the way to go with this, is it even possible or I am misinterpreting something.</p>