Overfetching problem when querying related entities and using intermediary projections

[aradalvand]

Following #2365 (comment)
I have Book and Author domain classes + BookDto and AuthorDto classes, I want to expose an IQueryable<BookDto> for the books field on my query root type.
So here is the GetBooks method on the Query class: It simply projects Book into BookDto and returns an IQueryable<BookDto> so that Hot Chocolate could then do its magic on it.

[UseSelection]
public IQueryable<BookDto> GetBooks([Service]AppDbContext dbContext)
{
    return dbContext.Books.Select(book => new BookDto
    {
        Name = book.Name,
        ...
        Author = new AuthorDto
        {
            Id = book.Author.Id,
            Name = book.Author.Name,
        }
    });
}

Now, when you run the following query:

{
  books {
    name
    author {
      name
    }
  }
}

You would probably expect the following SQL to ultimately get generated:

SELECT [b].[Name], [a].[Name]
      FROM [Books] AS [b]
      INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

However, instead, Hot Chocolate causes this one to get generated by EF Core:

SELECT [b].[Name], [a].[Id], [a].[Name], [a].[Age]
      FROM [Books] AS [b]
      INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

Which retrieves all the fields on the author (including Age and Id in this case), even though the query requested only the Name.

At first, I thought this must be an EF Core issue, but then I executed the following code:

var queryableDto = dbContext.Books.Select(book => new BookDto
{
    Name = book.Name,
    Author = new AuthorDto
    {
        Id = book.Author.Id,
        Name = book.Author.Name,
        Age = book.Author.Age
    }
});
// The code that you would expect Hot Chocolate will generate for the above GraphQL query:
queryableDto.Select(bDto => new
{
    Name = bDto.Name,
    Author = new
    {
        Name = bDto.Author.Name,
    }
}).ToList();

And I realized it works as expected, and generates the following SQL which only selects the requested column (author name in this case):

SELECT [b].[Name], [a].[Name]
      FROM [Books] AS [b]
      INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

Therefore, this must be related to Hot Chocolate, I believe.
This is really a serious problem for situations like mine when we don't want to expose IQueryable<TheEntityFrameworkModelClass> but rather a different type.

I'm using Hot Chocolate version 10.5.2
And I've tried this with both EF Core version 3.1.8 and 5.0.0 RC.

[PascalSenn]

i will track this down in the new implementation for 11.

It should just work, because we prjoect like this:

{
  books {
    name
    author {
      name
    }
  }
}

.Select(x=> new BookDto
            {
                Name = x.Name,
                Author = new AuthorDto
                { 
                    Name = x.Author.Name,
                }
            })

[aradalvand]

@PascalSenn Ok, great to hear. Then we'll have to wait for v11 before we can deploy our app so that this issue is fixed until then. Thanks.

It should just work, because we prjoect like this:

Well, as I mentioned here, running the code that you said that Hot Chocolate would generate from the GraphQL query:

queryableDto.Select(bDto => new
{
    Name = bDto.Name,
    Author = new
    {
        Name = bDto.Author.Name,
    }
}).ToList();

would NOT generate the SQL that Hot Chocolate causes to be generated. I talked about this in the original comment and also here. So I think that's probably not exactly how the projection works in Hot Chocolate.
And I feel like this might be a little mistake or something somewhere, it's probably not that big of a deal on your side, I don't know, I'm just guessing! But it is, however, a very big deal for our side! Since all the data of a related entity gets fetched unnecessarily when you request even just one column. So, I'd appreciate it if you look into this in the coming days! Thank you so much.

[PascalSenn]

Does this query generate the desired result?

queryableDto.Select(x=> new BookDto
            {
                Name = x.Name,
                Author = new AuthorDto
                { 
                    Name = x.Author.Name,
                }
            }).ToList();

[aradalvand]

@PascalSenn Yes, that query would generate the following SQL:

SELECT [b].[Name], [a].[Name]
      FROM [Books] AS [b]
      INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

which is perfect. It only retrieves the name of the book and the name of the author, as requested.

However, sending this GraphQL query to our Hot Chocolate server:

{
  books {
    name
    author {
      name
    }
  }
}

would generate this SQL instead:

SELECT [b].[Name], [a].[Id], [a].[Name], [a].[Age]
      FROM [Books] AS [b]
      INNER JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

which as you can see is unnecessarily fetching the Id and Age of the author, while they were not queried.

I can also create a public GitHub repo and push this project into it so you can clone it and look into it and into the generated queries yourself. A super-simple project with less than 15 classes in total.

[PascalSenn]

Yes a repro would be great

Are there any custom resolvers on author?

[aradalvand]

@PascalSenn Sure! I'll do it and let you know.
And no, nothing, it's a just class with a handful of properties. I've added nothing HotChocolate-specific to it at all.
I'll create the repo shortly ;)

[aradalvand]

@PascalSenn Here you go, sir: https://github.com/AradAral/AspNetCoreGraphQL

Edit: I added some seed data for the database so if you want to clone the project you can just adjust the connection string and then run one initial migration, as you know, and then you can use the GraphQL endpoint and inspect the generated SQL queries. I also added a MyController, which manually does the querying; you can check the query that that generates too.
Thanks in advance.

[aradalvand]

@PascalSenn: :)

[PascalSenn]

@AradAral cool, the repro is definitely helpful.
Checked the configuration didn't see anything obvious wrong with it.

[aradalvand]

@PascalSenn I just noticed something else which I think might be important:
I removed the UseSelection middleware from the GetBooks method, but it still gave me the author data if I queried it, which normally wouldn't happen unless you use the UseSelection middleware.
Do you think that can tell us anything about the issue?