search

ChilliCream / graphql-platform

Welcome to the home of the Hot Chocolate GraphQL server for .NET, the Strawberry Shake GraphQL client for .NET and Banana Cake Pop the awesome Monaco based GraphQL IDE.
https://chillicream.com
MIT License
5.28k stars 748 forks source link

Selection over automapper dto #2002

Closed koraycsharp closed 2 years ago

koraycsharp commented 4 years ago

When using Hotchocolate and Automapper, unnecessary database queries occur in the selection process.

Here is my dtos

public class GetSchoolResponse
{
        public string Name { get; set; }
        public string Address { get; set; }
        public ICollection<GetClassroom> Classrooms { get; set; }
}

public class GetClassroom
{
        public string Name { get; set; }
        public int? Floor { get; set; }
        public ICollection<GetStudent> Students { get; set; }
}

public class GetStudent
{
        public string Name { get; set; }
        public string Surname { get; set; }
}

And here is my mappings

CreateMap<GetSchoolResponse, SchoolEntity>().ReverseMap();
CreateMap<GetClassroom, ClassroomEntity>().ReverseMap();
CreateMap<GetStudent, StudentEntity>().ReverseMap();

And here is my query

[UseSelection]
public  IQueryable<GetSchoolResponse> GetSchoolById(int id, [Service] ISchoolRepository repository)
{
        return repository
                .Include(dr => dr.Classrooms)
                .ThenInclude(dr => dr.Students)
                .Where(dr => dr.Id == id)
                .OrderBy(dr => dr.Id)
                .ProjectTo<GetSchoolResponse>(Mapper.ConfigurationProvider);
}

And finally my graphql query

query
{
  schoolById(id:2)
  { 
    classrooms{
      floor, students{
        name
      }
    }
  }
}

This query return me this db queries

1) Db query for school

SELECT Id]
FROM [EDUCATION].[School]
WHERE ([Id] = @__request_Id_0)
ORDER BY [Id]

2) Db query for classroom

SELECT [Floor], [Name], [Id]
FROM [EDUCATION].[Classroom]
WHERE  (@_outer_Id = [SchoolId])

3) Db queries for students

SELECT [Name], [Surname]
FROM [EDUCATION].[Student] 
WHERE  (@_outer_Id1 = [ClassroomId])
SELECT [Name], [Surname]
FROM [EDUCATION].[Student] 
WHERE  (@_outer_Id1 = [ClassroomId])

Select query for school is normal. Only id return me but selection over classroom and student dtos is not acting as desired i think. Because i want only floor and name fields in my graphql query but db return me name and surname field for student table and floor and name for classroom table.

PascalSenn commented 4 years ago

with use seclection you can get rid of these:


                .Include(dr => dr.Classrooms)
                .ThenInclude(dr => dr.Students)

regarding your problem, i am not aware of the internals of Automapper. How does the proejction look like? Can you add a middleware before UseSelection and validate what is in context.Result after you called next?

damikun commented 4 years ago

I did similar think using auto mapper without problems.. I posted this on Slack channel maybe it will help you it was related to eliminate EF-core middle tables but i use auto-mapper for it.. In just copy paste it from slack:

The thing what i tried to solve was elimination of Join (middle) tables of many-to-many relation of EF-Core and projection to my custom DTO for graphql server..

Some picture what im talking about: https://excalidraw.com/#json=5532829123346432,9UuB9AkYRqZDtTW4TGTLGQ

I will write what i did to get what i have and why:

  1. First i started with schema generation from Hotchocolate IQueryable but i realised, that this is not the way to provide ef-core data model to graphql directly.... good maybe for small Data sets but my model have about 50 separate data tables and they are nested to each other with a lot "many-to-many" relations and the way how graph models looks after was not good for me..
  2. Then i create my custom dto model and i use resolvers to get data... This was working but this was not efficient and after short time i start to implement data loaders and the code become big and hard to understand.. The bigest problem was solving N+1 and optimization of Query to SQL.. The fetching was working but the how SQL select looks like was terrible.. (how i implemented)
  3. Solution: I start to search for something what will help me to map my custom Graphql DTO model to DB model using IQueryable to reduce all join tables and to have ability to select and query data what i need.. I found Automapper IQueryable projection extension and after some time stuff works !

So now i have 3 blocks: HotchocolateGraphQL > IQueryable Mapper > EF Core

Good thing is that graphql server have no idea there is some mapper he is performing all operations on DTO model and mapper transform it to EF-core model and all is IQueryable

I will add small example:

Startup:


 var mappingConfig = new MapperConfiguration(mc => {
                mc.AddProfile(new MappingProfile());
            });
            IMapper mapper = mappingConfig.CreateMapper();
            services.AddSingleton(mapper);
            services.AddGraphQL(
                   SchemaBuilder.New()
                    .AddQueryType<UserQuerry>()
                    .Create(), new QueryExecutionOptions { ForceSerialExecution = true }).AddErrorFilter(error => {
                        Console.WriteLine(error.Exception);
                        return error;
                    });

Querry:

 public class UserQuerry {
        [UseSelection]
        public IQueryable<User_DTO> GetGraphUsers([Service] IAppDbContext context, [Service] IMapper mapper) =>
            mapper.ProjectTo<User_DTO>(context.Users.AsNoTracking());
    }

Mapping: 


namespace Aplication.GraphQL.Mapping {
    public class MappingProfile : Profile {
        public MappingProfile() {
            // ------------------------------
            //  Mapping configuration
            // ------------------------------
            CreateMap<User, User_DTO>()
                .ForMember(dest => dest.Code, opt => opt.MapFrom(src => src.Code))
                .ForMember(dest => dest.FirstName, opt => opt.MapFrom(src => src.FirstName))
                .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.ID))
                .ForMember(dest => dest.LastName, opt => opt.MapFrom(src => src.LastName))
                .ForMember(dest => dest.UserName, opt => opt.MapFrom(src => src.UserName))
                .ForMember(dest => dest.Roles, opt => opt.MapFrom(src => src.Roles.Select(e => e.Role)))
                .ForMember(dest => dest.Groups, opt => opt.MapFrom(src => src.Groups.Select(e => e.Group)))
                .ForMember(dest => dest.Premissions, opt => opt.MapFrom(src => src.Claims.Select(e => e.Permission)));
            CreateMap<Role_DTO, Role>()
                .ForMember(dest => dest.Code, opt => opt.MapFrom(src => src.Code))
                .ForMember(dest => dest.Description, opt => opt.MapFrom(src => src.Description))
                .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.ID))
                .ForMember(dest => dest.Name, opt => opt.MapFrom(src => src.Name))
                .ReverseMap();
            CreateMap<Group, Group_DTO>()
                .ForMember(dest => dest.Code, opt => opt.MapFrom(src => src.Code))
                .ForMember(dest => dest.ColorCode, opt => opt.MapFrom(src => src.ColorCode))
                .ForMember(dest => dest.Description, opt => opt.MapFrom(src => src.Description))
                .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.ID))
                .ForMember(dest => dest.Name, opt => opt.MapFrom(src => src.Name))
                .ForMember(dest => dest.Premissions, opt => opt.MapFrom(src => src.Claims.Select(e => e.Permission)))
                .ReverseMap();
            CreateMap<Permission, Premission_DTO>()
                .ForMember(dest => dest.ID, opt => opt.MapFrom(src => src.ID))
                .ForMember(dest => dest.Key, opt => opt.MapFrom(src => src.Key))
                .ForMember(dest => dest.Value, opt => opt.MapFrom(src => src.Value))
                .ReverseMap();
        }
    }
}

Querry only user name:


{
  graphUsers{
    firstName
  }
}

Result:


info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.2 initialized 'AppDbContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "u"."FirstName"
      FROM "Users" AS "u"
info: Aplication.Services.MetricHandler[0]

Querry more data example:


{
  graphUsers{
    firstName
    groups{
      iD
    }
  }
}

Result:


info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "u"."FirstName", "u"."ID", "t"."ID", "t"."GroupID", "t"."UserID"
      FROM "Users" AS "u"
      LEFT JOIN (
          SELECT "g0"."ID", "g"."GroupID", "g"."UserID"
          FROM "GroupUsers" AS "g"
          INNER JOIN "Groups" AS "g0" ON "g"."GroupID" = "g0"."ID"
      ) AS "t" ON "u"."ID" = "t"."UserID"
      ORDER BY "u"."ID", "t"."GroupID", "t"."UserID", "t"."ID"

Some deeper DTO querry example projected to EFcore model select..


info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "u"."ID", "u"."Deactivated", "u"."Email", "u"."FirstName", "u"."LastName", "t0"."Code", "t0"."ColorCode", "t0"."Description", "t0"."ID", "t0"."Name", "t0"."GroupID", "t0"."UserID", "t0"."ID0", "t0"."Key", "t0"."Value", "t0"."GroupID0", "t0"."PermissionID", "t1"."ID", "t1"."Key", "t1"."Value", "t1"."UserID", "t1"."PermissionID", "t2"."Code", "t2"."Description", "t2"."ID", "t2"."Name", "t2"."UserID", "t2"."RoleID"
      FROM "Users" AS "u"
      LEFT JOIN (
          SELECT "g0"."Code", "g0"."ColorCode", "g0"."Description", "g0"."ID", "g0"."Name", "g"."GroupID", "g"."UserID", "t"."ID" AS "ID0", "t"."Key", "t"."Value", "t"."GroupID" AS "GroupID0", "t"."PermissionID"
          FROM "GroupUsers" AS "g"
          INNER JOIN "Groups" AS "g0" ON "g"."GroupID" = "g0"."ID"
          LEFT JOIN (
              SELECT "p"."ID", "p"."Key", "p"."Value", "g1"."GroupID", "g1"."PermissionID"
              FROM "GroupPermissions" AS "g1"
              INNER JOIN "Permissions" AS "p" ON "g1"."PermissionID" = "p"."ID"
          ) AS "t" ON "g0"."ID" = "t"."GroupID"
      ) AS "t0" ON "u"."ID" = "t0"."UserID"
      LEFT JOIN (
          SELECT "p0"."ID", "p0"."Key", "p0"."Value", "u0"."UserID", "u0"."PermissionID"
          FROM "UserPermissions" AS "u0"
          INNER JOIN "Permissions" AS "p0" ON "u0"."PermissionID" = "p0"."ID"
      ) AS "t1" ON "u"."ID" = "t1"."UserID"
      LEFT JOIN (
          SELECT "r"."Code", "r"."Description", "r"."ID", "r"."Name", "u1"."UserID", "u1"."RoleID"
          FROM "UserRoles" AS "u1"
          INNER JOIN "Roles" AS "r" ON "u1"."RoleID" = "r"."ID"
      ) AS "t2" ON "u"."ID" = "t2"."UserID"
      ORDER BY "u"."ID", "t0"."GroupID", "t0"."UserID", "t0"."ID", "t0"."GroupID0", "t0"."PermissionID", "t0"."ID0", "t1"."UserID", 
"t1"."PermissionID", "t1"."ID", "t2"."UserID", "t2"."RoleID", "t2"."ID"
damikun commented 4 years ago

Just for info.. if you use [UseSelection] you dont need to specified this:

                .Include(dr => dr.Classrooms)
                .ThenInclude(dr => dr.Students)
                .Where(dr => dr.Id == id)
                .OrderBy(dr => dr.Id)

Just IQuriable of ProjectTo ...

JPVenson commented 3 years ago

@damikun What version of HotChocolate and Automapper are you using? I tried the same and Automapper always expands my whole NavigationProperty tree

damikun commented 3 years ago

I'm not using any UseSelection (old-naming in v10) or UseProjections (current naming in V11+) so i cannot fully say what is the aspect of your problems.. Dont forget you must use ProjectTo and not Map and lazyloaded property must be marked as virtual... You need to call it on iQueriable not on dataloader! What i remember nasted object in that time didn't get loaded but all basic fields of root object was all loaded (string/int/enum)...

JPVenson commented 3 years ago

But your example has the [UseSelection] attribute. I am using the Mapper.ProjectTo method and according to the AutoMapper documentation https://docs.automapper.org/en/stable/Queryable-Extensions.html#explicit-expansion this behavior is by design. It should not matter if I am using EF lazy loading or not because Automapper will actively request the navigation properties.

My Query method is just about the same as yours:

    [UsePaging]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IQueryable<AddressModel> GetAddresses([Service] CacheDbContext db, [Service]IMapperService mapperService)
    {
        return db.Addresses
            .AsSingleQuery()
            .AsNoTracking()
            .ProjectTo<AddressModel>(mapperService.ViewModelMapper.ConfigurationProvider);
    }

But as ProjectTo will expand all my mappings in that step, the query method does provide an IQuerable where everything is already expandend.

I am currently in a discussion on Stackoverflow with @PascalSenn https://stackoverflow.com/questions/67284037/using-automapper-10-1-1-and-hotchocolate-11-1-to-map-queryables/67284493?noredirect=1#comment118930085_67284493

If you have some idea on his approach it would be highly appreciated

damikun commented 3 years ago

Example is one year old... On that time i was on V10... This example is totally isolation of HC and automapper so they do not know about each other and are not connected only interface is ProjectTo abstraction... So it should work also in V11...

Try to just call your automapper ProjectTo directly for test if your mapping configuration does not miss some settings.. Normally only selected fields should be queried from DB.. This was real example from application what i posted hire in that time..

PascalSenn commented 2 years ago

Can you try once #4563 is merged if it works with the new extension method?

  1. This will only work when you use HotChocolate.Data
  2. dotnet add package HotChocolate.Data.AutoMapper
  3. Rewrite the mapping to: 
        [UseProjection]
    public IQueryable<PostDto> GetPosts(BloggingContext dbContext, IResolverContext context)
        => dbContext.Posts.ProjectTo<Post, PostDto>(context);
JPVenson commented 2 years ago

@PascalSenn #4563 looks like exactly what is required to get Automapper working with EF and HotChocolate. Unfortunately I am currently not in any project that plans to use HotChocolate but if i got the time, I will try to test it with one of my OpenSource projects.

hassanrazakhalid commented 4 months ago

Hi i tried AutoMapper 13.0.1 and HotChocolate 13.9.5. It works fine but there is difference in Queries. Without AutoMapper i feel the query is more optimized. See below

(With Automapper)

public class GetShippersResponseMapping : Profile
{
    public GetShippersResponseMapping()
    {
        CreateMap<Shipper, GetShippersResponse>()
            .ForMember(dest => dest.Id, opt => opt.MapFrom(src => src.Id.ToString()))
            .ForMember(dest => dest.LegalName, opt => opt.MapFrom(src => src.LegalName))
            .ForMember(dest => dest.Admin, opt => opt.MapFrom(src => src.Users.FirstOrDefault(x => x.UserRoles.Any(ur => ur.Role == Role.ShipperAdmin))))
            .ReverseMap();
    }
}
SELECT CONVERT(varchar(20), [s].[Id]), [s].[LegalName], CASE
    WHEN EXISTS (
        SELECT 1
        FROM [AspNetUsers] AS [a]
        WHERE [s].[Id] = [a].[ShipperId] AND EXISTS (
            SELECT 1
            FROM [AppUserRoles] AS [a0]
            WHERE [a].[Id] = [a0].[UserId] AND [a0].[Role] = N'ShipperAdmin')) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, (
    SELECT TOP(1) [a1].[Id]
    FROM [AspNetUsers] AS [a1]
    WHERE [s].[Id] = [a1].[ShipperId] AND EXISTS (
        SELECT 1
        FROM [AppUserRoles] AS [a2]
        WHERE [a1].[Id] = [a2].[UserId] AND [a2].[Role] = N'ShipperAdmin')), (
    SELECT TOP(1) [a3].[UserName]
    FROM [AspNetUsers] AS [a3]
    WHERE [s].[Id] = [a3].[ShipperId] AND EXISTS (
        SELECT 1
        FROM [AppUserRoles] AS [a4]
        WHERE [a3].[Id] = [a4].[UserId] AND [a4].[Role] = N'ShipperAdmin'))
FROM [Shippers] AS [s]
WHERE [s].[IsDeleted] = CAST(0 AS bit)

No Automapper direct select 


await _dbContext.Shippers.AsNoTracking()
            .Where(x => !x.IsDeleted)
            .Select(x => new
            {
                x.Id,
                x.LegalName,
                Admin = x.Users.FirstOrDefault(u => u.UserRoles.Any(ur => ur.Role == Role.ShipperAdmin))
            })
            .ToListAsync(cancellationToken);
SELECT [s].[Id], [s].[LegalName], [t0].[Id], [t0].[AccessFailedCount], [t0].[ConcurrencyStamp], [t0].[CreatedAt], [t0].[CreatedByUserId], [t0].[Email], [t0].[EmailConfirmed], [t0].[IsDeleted], [t0].[IsSuspended], [t0].[LockoutEnabled], [t0].[LockoutEnd], [t0].[NormalizedEmail], [t0].[NormalizedUserName], [t0].[PasswordHash], [t0].[PhoneNumber], [t0].[PhoneNumberConfirmed], [t0].[SecurityStamp], [t0].[ShipperId], [t0].[TwoFactorEnabled], [t0].[UpdatedAt], [t0].[UpdatedByUserId], [t0].[UserName]
FROM [Shippers] AS [s]
LEFT JOIN (
    SELECT [t].[Id], [t].[AccessFailedCount], [t].[ConcurrencyStamp], [t].[CreatedAt], [t].[CreatedByUserId], [t].[Email], [t].[EmailConfirmed], [t].[IsDeleted], [t].[IsSuspended], [t].[LockoutEnabled], [t].[LockoutEnd], [t].[NormalizedEmail], [t].[NormalizedUserName], [t].[PasswordHash], [t].[PhoneNumber], [t].[PhoneNumberConfirmed], [t].[SecurityStamp], [t].[ShipperId], [t].[TwoFactorEnabled], [t].[UpdatedAt], [t].[UpdatedByUserId], [t].[UserName]
    FROM (
        SELECT [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[CreatedAt], [a].[CreatedByUserId], [a].[Email], [a].[EmailConfirmed], [a].[IsDeleted], [a].[IsSuspended], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[ShipperId], [a].[TwoFactorEnabled], [a].[UpdatedAt], [a].[UpdatedByUserId], [a].[UserName], ROW_NUMBER() OVER(PARTITION BY [a].[ShipperId] ORDER BY [a].[Id]) AS [row]
        FROM [AspNetUsers] AS [a]
        WHERE EXISTS (
            SELECT 1
            FROM [AppUserRoles] AS [a0]
            WHERE [a].[Id] = [a0].[UserId] AND [a0].[Role] = N'ShipperAdmin')
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON [s].[Id] = [t0].[ShipperId]
WHERE [s].[IsDeleted] = CAST(0 AS bit)

This could most probably create a problem in future, with more data. With Automapper, query has cases and other one has not. I'm not sure why is this the case as of now

@damikun @PascalSenn

hassanrazakhalid commented 4 months ago

An update, after some testing, i don't think its an Automapper issue, even when i return IQueriable and do manual mapping without Automapper, it is still generating same query as of when using Automapper. This has something to do with [UseProjection]

[UseProjection]
    [UseFiltering]
    public async Task<IQueryable<GetShippersResponse>> GetShippers(
        [Service] IAppDbContext dbContext,
        [Service] ISender sender,
        IResolverContext context)
    {
        await Task.CompletedTask;
        var shippers = dbContext.Shippers.AsNoTracking()
            .Where(x => !x.IsDeleted)
            .Select(x => new GetShippersResponse
            {
                Id = x.Id.ToString(),
                Address = x.Address,
                DisplayName = x.DisplayName,
                LegalName = x.LegalName,
                EmailAddress = "dymmy",
                PhoneNumber = x.PhoneNumber,
                IsLinkedWithTFM = true,
                Admin = x.Users
                    .Select(user => new AppUserDto
                    {
                        Id = user.Id.ToString(),
                        UserName = user.UserName,
                        Roles = user.UserRoles.Select(r => r.Role).ToArray()
                    })
                    .FirstOrDefault(userDto => userDto.Roles.Any(ur => ur == Role.ShipperAdmin))
            })
            ;
        return shippers;
    }