我目前正在探索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继承自>而不是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方法,用于创建选择Expression以在LINQ查询中使用。助手使用解析器的context.SubFields属性获取所需字段。
问题在于你只需要在每个查询级别的叶子上说一些查询“specializations”与“SpecializationName”和“Code”,以及他们的“Name”和其他医生。在这种情况下,在
RootQuery
专业领域的解析器中,您只需要对Specializations
实体进行投影:SpecializationName
和Code
,然后当它进入从SpecializationType
中的“doctors”字段中提取所有Doctors
时,解析器的上下文具有应用于Doctor
的投影所需的不同SubFields。上述问题是,即使您没有使用查询批处理,我想
SpecializationType
中的Doctors
字段仍然需要在RootQuery
专业字段中获取SpecializationId。 在专业类型中。我觉得我没有很好地解释我经历了什么。
基线是据我所知,我们必须动态创建选择器,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;
}
}
这篇文章已经变得相当长了,很抱歉跨度如此之大。