.NET GraphQL Hot Chocolate Projections only working for requested List of Entities but not for requested Single Entities

Question

So if i understood it correctly Projections are used to get rid of the Over/ Under fetching problems you would have with normal REST-APIs.

I already implemented a GetAll Functionality for my Author-ObjectType, and there it works pretty fine. My Problem is I wont get it working for my GetById Functionality.

What I mean with not working is that always the full SQL statement gets fired into the database and not only the requested fields are selected.

Maybe I understood it wrong and Projections are only useable for a list of Entities? or in this case IQueryables.

If this is only useable for Lists / IQueryables, what would be a way to implement it for filtered Entities (like if I would want a Author by ID or name, etc.)

CallHierarchy: AuthorQuery -> AuthorService -> Repository

AuthorQuery:

[ExtendObjectType(typeof(Query))]
public class AuthorQuery {
    [UseProjection]
    public async Task<IQueryable<Author>> Authors([Service] IAuthorService authorService) {
        return await authorService.GetAsync();
    }

    [UseProjection]
    public async Task<Author> AuthorById([Service] IAuthorService authorService, int id) {
        var result = await authorService.GetAsync(author => author.Id == id);
        return result.Single();
    }
}

AuthorService (at this Point just the base service cause AuthorService calls the parent method):

public class BaseService<TEntity> : IBaseService<TEntity> where TEntity : BaseEntity {
    protected readonly IRepository<TEntity> repository;

    public BaseService(IRepository<TEntity> repository) {
        this.repository = repository;
    }
    public virtual async Task<IQueryable<TEntity>> GetAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
        return await repository.GetAsync(filter, includes);
    }

    public virtual async Task<TEntity> GetFirstAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
        return await repository.GetFirstAsync(filter, includes);
    }
}

public class Repository<TEntity> : IRepository<TEntity> where TEntity : BaseEntity {
    protected readonly LibraryContext context;
    public Repository(IDbContextFactory<LibraryContext> contextFactory) {
        this.context = contextFactory.CreateDbContext();
    }

    public async Task<IQueryable<TEntity>> GetAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
        IQueryable<TEntity> query = context.Set<TEntity>();

        foreach (var include in includes) {
            query = query.Include(include);
        }

        if (filter != null) {
            query = query.Where(filter);
        }

        return query.AsQueryable();
    }

    public async Task<TEntity> GetFirstAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
        IQueryable<TEntity> query = context.Set<TEntity>();

        foreach (var include in includes) {
            query = query.Include(include);
        }

        if (filter != null) {
            query = query.Where(filter);
        }

        return await query.AsQueryable().FirstOrDefaultAsync();
    }
}

AuthorType Definition:

public class AuthorType: ObjectType<Author> { }

Program.cs --> only the Definition of Services and GraphQL specific stuff

builder.Services.AddTransient(typeof(IRepository<>), typeof(Repository<>));
builder.Services.AddTransient(typeof(IBookRepository), typeof(BookRepository));
builder.Services.AddTransient(typeof(IAuthorRepository), typeof(AuthorRepository));
builder.Services.AddTransient(typeof(IBaseService<>), typeof(BaseService<>));
builder.Services.AddTransient<IBookService, BookService>();
builder.Services.AddTransient<IAuthorService, AuthorService>();

builder.Services
        .AddGraphQLServer()
        .AddProjections()
        .AddQueryType<Query>()
        .AddTypeExtension<BookQuery>()
        .AddTypeExtension<AuthorQuery>()
        .AddMutationType<Mutation>()
        .AddTypeExtension<BookMutation>()
        .AddTypeExtension<AuthorMutation>()
        .AddType<BookType>()
        .AddType<AuthorType>()
        .AddType<BookCreate>()
        .AddType<BookUpdate>()
        .AddType<AuthorCreate>()
        .AddType<AuthorUpdate>();

This are the generated sql statements for the following requested fields:

{
  id
  firstName        
  books {
    id
    title
  }
}

Update: (generated sql querys)

ByAuthorId:

LastName is in Query

SELECT [a].[Id], [a].[FirstName], [a].[LastName], [t].[AuthorsId], [t].[BooksId], [t].[Id], [t].[Title]
FROM [Authors] AS [a]
LEFT JOIN (
    SELECT [a0].[AuthorsId], [a0].[BooksId], [b].[Id], [b].[Title]
    FROM [AuthorBook] AS [a0]
    INNER JOIN [Books] AS [b] ON [a0].[BooksId] = [b].[Id]
) AS [t] ON [a].[Id] = [t].[AuthorsId]
WHERE [a].[Id] = @__id_0
ORDER BY [a].[Id], [t].[AuthorsId], [t].[BooksId]

LastName has been ignored GetAll:

SELECT [a].[Id], [a].[FirstName], [t].[Id], [t].[Title], [t].[AuthorsId], [t].[BooksId]
FROM [Authors] AS [a]
LEFT JOIN (
    SELECT [b].[Id], [b].[Title], [a0].[AuthorsId], [a0].[BooksId]
    FROM [AuthorBook] AS [a0]
    INNER JOIN [Books] AS [b] ON [a0].[BooksId] = [b].[Id]
) AS [t] ON [a].[Id] = [t].[AuthorsId]
ORDER BY [a].[Id], [t].[AuthorsId], [t].[BooksId]

Update (2) Code from BaseService, AuthorService and AuthorRepository

public class BaseService<TEntity> : IBaseService<TEntity> where TEntity : BaseEntity {
        protected readonly IRepository<TEntity> repository;

        public BaseService(IRepository<TEntity> repository) {
            this.repository = repository;
        }
        public virtual async Task<IQueryable<TEntity>> GetAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
            return await repository.GetAsync(filter, includes);
        }

        public virtual async Task<TEntity> GetFirstAsync(Expression<Func<TEntity, bool>> filter = null, params Expression<Func<TEntity, object>>[] includes) {
            return await repository.GetFirstAsync(filter, includes);
        }

        public virtual Task<TEntity> AddAsync(TEntity entity) {
            return repository.AddAsync(entity);
        }
        public virtual async Task<TEntity> UpdateAsync(TEntity entity) {
            return await repository.UpdateAsync(entity);
        }

        public virtual async Task<bool> ExistsAsync(int id) {
            return await repository.ExistsAsync(id);
        }

        public virtual async Task RemoveAsync(TEntity entity) {
            await repository.RemoveAsync(entity);
        }

    }

public class AuthorService : BaseService<Author>, IAuthorService {
    public AuthorService(IAuthorRepository repository) : base(repository) {
    }
}

AuthorRepository:

public class AuthorRepository : Repository<Author>, IAuthorRepository {
    public AuthorRepository(IDbContextFactory<LibraryContext> contextFactory) : base(contextFactory) { }

    public override async Task<Author> AddAsync(Author author) {
        author.Books = await context.Books.Where(book => author.Books.Select(x => x.Id).ToList().Contains(book.Id)).ToListAsync();
        return await base.AddAsync(author);
    }

    public override async Task<Author> UpdateAsync(Author author) {
        var authorToUpdate = await GetFirstAsync(a => a.Id == author.Id, a => a.Books);
        if (authorToUpdate == null) {
            throw new ArgumentNullException(nameof(authorToUpdate));
        }

        authorToUpdate.FirstName = author.FirstName;
        authorToUpdate.LastName = author.LastName;

        if (author.Books.Count != authorToUpdate.Books.Count || !authorToUpdate.Books.All(author.Books.Contains)) {
            authorToUpdate.Books.UpdateManyToMany(author.Books, b => b.Id);
            authorToUpdate.Books = await context.Books.Where(book => author.Books.Select(a => a.Id).ToList().Contains(book.Id)).ToListAsync();
        }

        return await base.UpdateAsync(authorToUpdate);
    }
}

please notice that I updated the AuthorById Function of AuthorQuery like the following, like it was suggested

[UseProjection]
[UseSingleOrDefault]
public async Task<IQueryable<Author>> AuthorById([Service] IAuthorService authorService, int id) {
    return await authorService.GetAsync(author => author.Id == id, author => author.Books);
}

Answer 1

Did you try this?

[ExtendObjectType(typeof(Query))]
public class AuthorQuery {
    [UseProjection]
    public async Task<IQueryable<Author>> Authors([Service] IAuthorService authorService) {
        return await authorService.GetAsync();
    }

    [UseSingleOrDefault]
    [UseProjection]
    public async Task<IQueryable<Author>> AuthorById([Service] IAuthorService authorService, int id) {
        return await authorService.GetAsync(author => author.Id == id);
    }
}