时间戳

首页 > 解决方案 > 带有 GraphQL 的 EF Core

c# - 带有 GraphQL 的 EF Core

问题描述

我目前正在探索 GraphQL 开发，我目前正在探索通过 EF Core 生成什么样的 SQL 查询，我观察到，无论我的 GraphQL 查询只包含几个字段，EF Core 都会为所有字段发送 SQL Select实体。

这是我现在使用的代码：

public class DoctorType : ObjectGraphType<Doctors>
{
    public DoctorType()
    {
            Field(d => d.PrefixTitle);
            Field(d => d.FName);
            Field(d => d.MName);
            Field(d => d.LName);
            Field(d => d.SufixTitle);
            Field(d => d.Image);
            Field(d => d.EGN);
            Field(d => d.Description);
            Field(d => d.UID_Code); 
    }
}

public class Doctors : ApplicationUser
{
    public string Image { get; set; }
    [StringLength(50)]
    public string UID_Code { get; set; }
}

我正在使用的查询是

{
  doctors{
    fName
    lName
  }
}

生成的 SQL 选择 Doctor 实体的所有字段。

有没有办法进一步优化从 EF Core 生成的 SQL 查询？

我猜这是因为 DoctorType 继承自ObjectGraphType<Doctors>而不是继承自 Doctor 的某个 Projection，但我想不出一个聪明的解决方法？

有什么建议么？

编辑：

我正在使用 Joe McBride 版本 2.4.0 的 GraphQL.NET (graphql-dotnet)

编辑2：

要么我做错了，要么我不知道。

作为评论之一，我下载了 SimonCropp 的 GraphQL.EntityFramework Nuget 包

我做了所有需要的配置：

        services.AddDbContext<ScheduleDbContext>(options =>
        {
            options.UseMySql(Configuration.GetConnectionString("DefaultConnection"));
        });

        using (var myDataContext = new ScheduleDbContext())
        {
            EfGraphQLConventions.RegisterInContainer(services, myDataContext);
        }

我的对象图类型如下

public class SpecializationType : EfObjectGraphType<Specializations>
{
    public SpecializationType(IEfGraphQLService graphQlService)
        :base(graphQlService)
    {
        Field(p => p.SpecializationId);
        Field(p => p.Code);
        Field(p => p.SpecializationName);
    }
}

我的查询看起来是：

public class RootQuery : EfObjectGraphType
{
    public RootQuery(IEfGraphQLService efGraphQlService,
        ScheduleDbContext dbContext) : base(efGraphQlService)
    {
        Name = "Query";

        AddQueryField<SpecializationType, Specializations>("specializationsQueryable", resolve: ctx => dbContext.Specializations);

    }
}

我正在使用这个 graphQL 查询

{
  specializationsQueryable
  {
    specializationName
  }
}

调试日志显示生成的 SQL 查询是

SELECT `s`.`SpecializationId`, `s`.`Code`, `s`.`SpecializationName`
FROM `Specializations` AS `s`

即使我只想要 specializationName 字段并且我希望它是：

SELECT `s`.`SpecializationName`
FROM `Specializations` AS `s`

更新

我想到目前为止我还不明白 graphQL 是如何工作的。我认为有一些幕后数据获取，但没有。

主要提取在查询的字段解析器中完成：

FieldAsync<ListGraphType<DoctorType>>("doctors", resolve: async ctx => await doctorServices.ListAsync());

并且只要解析器的结果是完整对象，在我的情况下，解析器返回Doctors实体列表，它将查询整个实体（所有字段）的数据库。如果您返回 IQueryable 或您正在查询的实体，GraphQL 没有开箱即用的优化并不重要。

这里的每个结论都是我的想法，它不是 100% 保证正确的

所以我所做的是创建一组 Helper 方法，这些方法正在创建一个选择表达式以在 LINQ 查询中使用。助手使用解析器的 context.SubFields 属性来获取所需的字段。

问题是您只需要查询的每个级别的叶子，例如一些带有“SpecializationName”和“代码”的查询“专业化”以及带有“名称”的“医生”等等。在这种情况下，在专业领域的RootQuery解析器中，您只需要实体Specializations投影：SpecializationName和CodeDoctorsSpecializationTypeDoctor

上面的问题是，当您使用查询批处理时，我猜即使您不认为该Doctors字段 SpecializationType需要在专业字段中获取的 SpecializationId RootQuery。

我想我没有很好地解释我经历了什么。

据我了解，基线是我们必须动态创建 linq 应该用来投影实体的选择器。

我在这里发布我的方法：

    public class RootQuery : EfObjectGraphType
{
    public RootQuery(IEfGraphQLService efGraphQlService, ISpecializationGraphQlServices specializationServices,
        IDoctorGraphQlServices doctorServices, ScheduleDbContext dbContext) : base(efGraphQlService)
    {
        Name = "Query";

        FieldAsync<ListGraphType<SpecializationType>>("specializations"
            , resolve: async ctx => {

                var selectedFields = GraphQLResolverContextHelpers.GetFirstLevelLeavesNamesPascalCase(ctx.SubFields);
                var expression = BuildLinqSelectorObject.DynamicSelectGenerator<Specializations>(selectedFields.ToArray());

                return await specializationServices.ListAsync(selector: expression);
            });
    }
}

专业化类型

 public class SpecializationType : EfObjectGraphType<Specializations>
{
    public SpecializationType(IEfGraphQLService graphQlService
        , IDataLoaderContextAccessor accessor, IDoctorGraphQlServices doctorServices)
        : base(graphQlService)
    {
        Field(p => p.SpecializationId);
        Field(p => p.Code);
        Field(p => p.SpecializationName);
        Field<ListGraphType<DoctorType>, IEnumerable<Doctors>>()
            .Name("doctors")
            .ResolveAsync(ctx =>
            {

                var selectedFields = GraphQLResolverContextHelpers.GetFirstLevelLeavesNamesPascalCase(ctx.SubFields);
                selectedFields = GraphQLResolverContextHelpers.AppendParrentNodeToEachItem(selectedFields, parentNode: "Doctor");
                selectedFields = selectedFields.Union(new[] { "Specializations_SpecializationId" });

                var expression = BuildLinqSelectorObject.BuildSelector<SpecializationsDoctors, SpecializationsDoctors>(selectedFields);

                var doctorsLoader = accessor.Context
                    .GetOrAddCollectionBatchLoader<int, Doctors>(
                        "GetDoctorsBySpecializationId"
                        , (collection, token) =>
                        {
                            return doctorServices.GetDoctorsBySpecializationIdAsync(collection, token, expression);
                        });
                return doctorsLoader.LoadAsync(ctx.Source.SpecializationId);
            });
    }
}

医生服务：

public class DoctorGraphQlServices : IDoctorGraphQlServices
{
    public ScheduleDbContext _dbContext { get; set; }

    public DoctorGraphQlServices(ScheduleDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<List<Doctors>> ListAsync(int? specializationId = null)
    {
        var doctors = _dbContext.Doctors.AsQueryable();

        if(specializationId != null)
        {
            doctors = doctors.Where(d => d.Specializations.Any(s => s.Specializations_SpecializationId == specializationId));
        }

        return await doctors.ToListAsync();
    }

    public async Task<ILookup<int, Doctors>> GetDoctorsBySpecializationIdAsync(IEnumerable<int> specializationIds, CancellationToken token, Expression<Func<SpecializationsDoctors, SpecializationsDoctors>> selector = null)
    {
        var doctors = await _dbContext.SpecializationsDoctors
            .Include(s => s.Doctor)
            .Where(spDocs => specializationIds.Any(sp => sp == spDocs.Specializations_SpecializationId))
            .Select(selector: selector)
            .ToListAsync();

        return doctors.ToLookup(i => i.Specializations_SpecializationId, i => i.Doctor);
    }

}

专业化服务

public class SpeciaizationGraphQlServices : ISpecializationGraphQlServices
{

    public ScheduleDbContext _dbContext { get; set; }

    public SpeciaizationGraphQlServices(ScheduleDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<dynamic> ListAsync(string doctorId = null, Expression<Func<Specializations, Specializations>> selector = null)
    {
        var specializations = _dbContext.Specializations.AsQueryable();

        if (!string.IsNullOrEmpty(doctorId))
        {
            specializations = specializations.Where(s => s.Doctors.Any(d => d.Doctors_Id == doctorId));
        }

        return await specializations.Select(selector).ToListAsync();

    }

    public async Task<ILookup<string, Specializations>> GetSpecializationsByDoctorIdAsync(IEnumerable<string> doctorIds, CancellationToken token)
    {
        var specializations = await _dbContext.SpecializationsDoctors
            .Include(s => s.Specialization)
            .Where(spDocs => doctorIds.Any(sp => sp == spDocs.Doctors_Id))
            .ToListAsync();

        return specializations.ToLookup(i => i.Doctors_Id, i => i.Specialization);
    }

    public IQueryable<Specializations> List(string doctorId = null)
    {
        var specializations = _dbContext.Specializations.AsQueryable();

        if (!string.IsNullOrEmpty(doctorId))
        {
            specializations = specializations.Where(s => s.Doctors.Any(d => d.Doctors_Id == doctorId));
        }

        return specializations;
    }
}

这篇文章已经变得相当大了，抱歉跨度..

标签： c#asp.net-coreentity-framework-coregraphqlgraphql-dotnet

解决方案

对于DoctorType，检查ObjectGraphType用于返回的定义Doctors。

例如，我有PlayerType如下所示： 

public class PlayerType : ObjectGraphType<Player>
{
    public PlayerType(ISkaterStatisticRepository skaterStatisticRepository)
    {
        Field(x => x.Id);
        Field(x => x.Name, true);
        Field(x => x.BirthPlace);
        Field(x => x.Height);
        Field(x => x.WeightLbs);
        Field<StringGraphType>("birthDate", resolve: context => context.Source.BirthDate.ToShortDateString());
        Field<ListGraphType<SkaterStatisticType>>("skaterSeasonStats",
            arguments: new QueryArguments(new QueryArgument<IntGraphType> { Name = "id" }),
            resolve: context => skaterStatisticRepository.Get(context.Source.Id), description: "Player's skater stats");
    }
}

我回来Field<ListGraphType<PlayerType>>了 

public class NHLStatsQuery : ObjectGraphType
{
    public NHLStatsQuery(IPlayerRepository playerRepository, NHLStatsContext dbContext)
    {
        Field<ListGraphType<PlayerType>>(
            "players",
            resolve: context => {
                return dbContext.Players.Select(p =>new Player { Id = p.Id, Name = p.Name });
                //return playerRepository.All();
            });
    }
}

对于查询及其列，它由resolvein Field 控制。

无论您要返回什么字段，请确保在 中定义的列PlayerType返回resolve.

推荐阅读