epam / epam/epam-graphql

Nested loader causes N+1 queries (instead of two) in some circumstances

Open
#35 0 comments 0 reactions 0 assignees View on GitHub
bug
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.