Nested loader causes N+1 queries (instead of two) in some circumstances
- Dominant language
- C#
- Stars
- 23
- Forks
- 6
- PR merge metrics
- No merged PRs in 30d
Description
### Expected Behavior
Both graphql queries below should generate identical SQL.
### Current Behavior
Run suggested project. It produces the following SQL (can be seen in a standard output):
```sql
SELECT "d"."Id" AS "id", "d"."CreatedById" AS "<>createdBy$1"
FROM "Departments" AS "d"
ORDER BY "d"."Id"
SELECT "u"."Id" AS "Key"
FROM "Users" AS "u"
WHERE "u"."Id" IN (1, 2)
ORDER BY "u"."Id"
```
For now, everything looks OK. Change a graphql query to the following:
```graphql
query {
departments {
items {
id
createdBy {
id
}
createdBy {
id
}
}
}
}
```
For this query, generated SQL contains N+1 queries, where N is count of departments.
```sql
SELECT "d"."Id" AS "id", "d"."CreatedById" AS "<>createdBy$1"
FROM "Departments" AS "d"
ORDER BY "d"."Id"
SELECT "u"."Id" AS "Key"
FROM "Users" AS "u"
WHERE "u"."Id" IN (1)
ORDER BY "u"."Id"
SELECT "u"."Id" AS "Key"
FROM "Users" AS "u"
WHERE "u"."Id" IN (2)
ORDER BY "u"."Id"
SELECT "u"."Id" AS "Key"
FROM "Users" AS "u"
WHERE "u"."Id" IN (1)
ORDER BY "u"."Id"
```
### Steps to reproduce
1. Create new console project and add Epam.GraphQL, Epam.GraphQL.EntityFrameworkCore and Epam.GraphQL.SystemTextJson packages as dependencies
2. Add https://github.com/epam/epam-graphql/blob/main/samples/Epam.GraphQL.Samples.Data/Epam.GraphQL.Samples.Data.csproj as a referenced project
3. Add the following classes to a Program.cs:
```csharp
public class GraphQLExecutionContext
{
public GraphQLDbContext DbContext { get; set; }
}
public class UserLoader : Loader
{
protected override IQueryable GetBaseQuery(GraphQLExecutionContext context) =>
context.DbContext.Users;
protected override void OnConfigure()
{
Field(user => user.Id);
Field(user => user.FullName);
}
}
public class DepartmentLoader : Loader
{
protected override IQueryable GetBaseQuery(GraphQLExecutionContext context) =>
context.DbContext.Departments;
protected override void OnConfigure()
{
Field(unit => unit.Id);
Field("createdBy")
.FromLoader((d, u) => d.CreatedById == u.Id);
}
}
public class GraphQLQuery : Query
{
protected override void OnConfigure()
{
Connection("departments", query => query.OrderBy(d => d.Id));
}
}
class Program
{
static async Task Main()
{
using var ctx = new GraphQLDbContext();
var schemaExecuter = new SchemaExecuter();
var executionContext = new GraphQLExecutionContext
{
DbContext = ctx
};
var result = await schemaExecuter.ExecuteAsync(optionsBuilder => optionsBuilder
.WithExecutionContext(executionContext)
.WithDbContext(ctx)
.Query(@"
query {
departments {
items {
id
createdBy {
id
}
}
}
}"))
.ToStringAsync(indent: true)
.ConfigureAwait(false);
Console.WriteLine(result);
}
}
```
### Additional Context
_No response_
Contributor guide
No contributing guide indexed for this repository
Research direction
Create the console project described in the issue, reference Epam.GraphQL.Samples.Data.csproj, and use the loaders and Program.cs reproduction shown. Run both GraphQL queries and inspect standard output for the generated SQL. Done means the query with the repeated createdBy selection produces the same two SQL queries as the equivalent query, without per-department user queries.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp
- Domain
- api, databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100