title: EF Core with GraphQL
body: <p>I'm currently exploring the GraphQL development and I'm currently exploring what kind of SQL queries are Generated via EF Core and I observed that no matter that my GraphQL query includes only a few fields the EF Core sends SQL Select for all fields of the Entity.</p>
  
  <p>This is the code I'm using now:</p>
  
  <pre><code>public class DoctorType : ObjectGraphType&lt;Doctors&gt;
  {
      public DoctorType()
      {
              Field(d =&gt; d.PrefixTitle);
              Field(d =&gt; d.FName);
              Field(d =&gt; d.MName);
              Field(d =&gt; d.LName);
              Field(d =&gt; d.SufixTitle);
              Field(d =&gt; d.Image);
              Field(d =&gt; d.EGN);
              Field(d =&gt; d.Description);
              Field(d =&gt; d.UID_Code); 
      }
  }
  
  public class Doctors : ApplicationUser
  {
      public string Image { get; set; }
      [StringLength(50)]
      public string UID_Code { get; set; }
  }
  </code></pre>
  
  <p>the query I'm using is </p>
  
  <pre><code>{
    doctors{
      fName
      lName
    }
  }
  </code></pre>
  
  <p>The SQL generated selects all fields of the Doctor entity.</p>
  
  <p>Is there any way to further optimize that the generated SQL query from EF Core?</p>
  
  <p>I'm guessing this happens because the DoctorType inherits from <code>ObjectGraphType&lt;Doctors&gt;</code> and not from some Projection of the Doctor, but I can't think of a clever workaround of this?</p>
  
  <p>Any suggestions?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I'm using GraphQL.NET (graphql-dotnet) by Joe McBride version 2.4.0</p>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>Either I'm doing it wrong or I don't know.</p>
  
  <p>As one of the comments suggested i downloaded GraphQL.EntityFramework Nuget package by SimonCropp</p>
  
  <p>I did all the configuration needed for it:</p>
  
  <pre><code>        services.AddDbContext&lt;ScheduleDbContext&gt;(options =&gt;
          {
              options.UseMySql(Configuration.GetConnectionString("DefaultConnection"));
          });
  
          using (var myDataContext = new ScheduleDbContext())
          {
              EfGraphQLConventions.RegisterInContainer(services, myDataContext);
          }
  </code></pre>
  
  <p>My Object graph Type is looking as follows</p>
  
  <pre><code>public class SpecializationType : EfObjectGraphType&lt;Specializations&gt;
  {
      public SpecializationType(IEfGraphQLService graphQlService)
          :base(graphQlService)
      {
          Field(p =&gt; p.SpecializationId);
          Field(p =&gt; p.Code);
          Field(p =&gt; p.SpecializationName);
      }
  }
  </code></pre>
  
  <p>My query looks is:</p>
  
  <pre><code>public class RootQuery : EfObjectGraphType
  {
      public RootQuery(IEfGraphQLService efGraphQlService,
          ScheduleDbContext dbContext) : base(efGraphQlService)
      {
          Name = "Query";
  
          AddQueryField&lt;SpecializationType, Specializations&gt;("specializationsQueryable", resolve: ctx =&gt; dbContext.Specializations);
  
      }
  }
  </code></pre>
  
  <p>and I'm using this graphQL query</p>
  
  <pre><code>{
    specializationsQueryable
    {
      specializationName
    }
  }
  </code></pre>
  
  <p>The debug log show that the generated SQL query is</p>
  
  <pre><code>SELECT `s`.`SpecializationId`, `s`.`Code`, `s`.`SpecializationName`
  FROM `Specializations` AS `s`
  </code></pre>
  
  <p>even though I want only specializationName field and I'm expecting it to be:</p>
  
  <pre><code>SELECT `s`.`SpecializationName`
  FROM `Specializations` AS `s`
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I guess so far I didn't understand how graphQL really worked. I thought that there is some behind the scene fetch of data but there isn't.</p>
  
  <p>The primary fetch is done in the query's field resolver :</p>
  
  <pre><code>FieldAsync&lt;ListGraphType&lt;DoctorType&gt;&gt;("doctors", resolve: async ctx =&gt; await doctorServices.ListAsync());
  </code></pre>
  
  <p>and as long the result to the resolver is the full object in my case the resolver return List of <code>Doctors</code> entity, it will query the Database for the whole entity (all fields). No optimisations are done out of the box from GraphQL doesn't matter if you return IQueryable or else of the entity you are querying.</p>
  
  <p>Every conclusion here is thought of mine it is not 100% guaranteed right</p>
  
  <p>So what I've did is create a group of Helper methods which are creating an selection Expression to use in the LINQ query. The helpers are using resolver's context.SubFields property to get the fields needed.</p>
  
  <p>The problem is that you need for every level of the query only the leaves, say some query "specializations" with "SpecializationName" and "Code" and the "Doctors" with their "Name" and else. In this case in the <code>RootQuery</code> specializations field's resolver you need only the <code>Specializations</code> entity projection so: <code>SpecializationName</code> and <code>Code</code> , then when it goes to fetch all <code>Doctors</code> from the "doctors" Field in <code>SpecializationType</code> the resolver's context has different SubFields which should be used for the projection of the <code>Doctor</code>. </p>
  
  <p>The problem with the above is, when you use query batches i guess even if you dont the thing is that the <code>Doctors</code> Field in  <code>SpecializationType</code> needs the SpecializationId fetched in the <code>RootQuery</code> specializations Field.</p>
  
  <p>I guess i didn't explain good what i went through.</p>
  
  <p>Base line is as far as I understand we have to dynamically create selectors which the linq should use to project the entity.</p>
  
  <p>I'm posting my approach here:</p>
  
  <pre><code>    public class RootQuery : EfObjectGraphType
  {
      public RootQuery(IEfGraphQLService efGraphQlService, ISpecializationGraphQlServices specializationServices,
          IDoctorGraphQlServices doctorServices, ScheduleDbContext dbContext) : base(efGraphQlService)
      {
          Name = "Query";
  
          FieldAsync&lt;ListGraphType&lt;SpecializationType&gt;&gt;("specializations"
              , resolve: async ctx =&gt; {
  
                  var selectedFields = GraphQLResolverContextHelpers.GetFirstLevelLeavesNamesPascalCase(ctx.SubFields);
                  var expression = BuildLinqSelectorObject.DynamicSelectGenerator&lt;Specializations&gt;(selectedFields.ToArray());
  
                  return await specializationServices.ListAsync(selector: expression);
              });
      }
  }
  </code></pre>
  
  <p>SpecializationType</p>
  
  <pre><code> public class SpecializationType : EfObjectGraphType&lt;Specializations&gt;
  {
      public SpecializationType(IEfGraphQLService graphQlService
          , IDataLoaderContextAccessor accessor, IDoctorGraphQlServices doctorServices)
          : base(graphQlService)
      {
          Field(p =&gt; p.SpecializationId);
          Field(p =&gt; p.Code);
          Field(p =&gt; p.SpecializationName);
          Field&lt;ListGraphType&lt;DoctorType&gt;, IEnumerable&lt;Doctors&gt;&gt;()
              .Name("doctors")
              .ResolveAsync(ctx =&gt;
              {
  
                  var selectedFields = GraphQLResolverContextHelpers.GetFirstLevelLeavesNamesPascalCase(ctx.SubFields);
                  selectedFields = GraphQLResolverContextHelpers.AppendParrentNodeToEachItem(selectedFields, parentNode: "Doctor");
                  selectedFields = selectedFields.Union(new[] { "Specializations_SpecializationId" });
  
                  var expression = BuildLinqSelectorObject.BuildSelector&lt;SpecializationsDoctors, SpecializationsDoctors&gt;(selectedFields);
  
                  var doctorsLoader = accessor.Context
                      .GetOrAddCollectionBatchLoader&lt;int, Doctors&gt;(
                          "GetDoctorsBySpecializationId"
                          , (collection, token) =&gt;
                          {
                              return doctorServices.GetDoctorsBySpecializationIdAsync(collection, token, expression);
                          });
                  return doctorsLoader.LoadAsync(ctx.Source.SpecializationId);
              });
      }
  }
  </code></pre>
  
  <p>DoctorsServices:</p>
  
  <pre><code>public class DoctorGraphQlServices : IDoctorGraphQlServices
  {
      public ScheduleDbContext _dbContext { get; set; }
  
      public DoctorGraphQlServices(ScheduleDbContext dbContext)
      {
          _dbContext = dbContext;
      }
  
      public async Task&lt;List&lt;Doctors&gt;&gt; ListAsync(int? specializationId = null)
      {
          var doctors = _dbContext.Doctors.AsQueryable();
  
          if(specializationId != null)
          {
              doctors = doctors.Where(d =&gt; d.Specializations.Any(s =&gt; s.Specializations_SpecializationId == specializationId));
          }
  
          return await doctors.ToListAsync();
      }
  
      public async Task&lt;ILookup&lt;int, Doctors&gt;&gt; GetDoctorsBySpecializationIdAsync(IEnumerable&lt;int&gt; specializationIds, CancellationToken token, Expression&lt;Func&lt;SpecializationsDoctors, SpecializationsDoctors&gt;&gt; selector = null)
      {
          var doctors = await _dbContext.SpecializationsDoctors
              .Include(s =&gt; s.Doctor)
              .Where(spDocs =&gt; specializationIds.Any(sp =&gt; sp == spDocs.Specializations_SpecializationId))
              .Select(selector: selector)
              .ToListAsync();
  
          return doctors.ToLookup(i =&gt; i.Specializations_SpecializationId, i =&gt; i.Doctor);
      }
  
  }
  </code></pre>
  
  <p>SpecializationServices</p>
  
  <pre><code>public class SpeciaizationGraphQlServices : ISpecializationGraphQlServices
  {
  
      public ScheduleDbContext _dbContext { get; set; }
  
      public SpeciaizationGraphQlServices(ScheduleDbContext dbContext)
      {
          _dbContext = dbContext;
      }
  
      public async Task&lt;dynamic&gt; ListAsync(string doctorId = null, Expression&lt;Func&lt;Specializations, Specializations&gt;&gt; selector = null)
      {
          var specializations = _dbContext.Specializations.AsQueryable();
  
          if (!string.IsNullOrEmpty(doctorId))
          {
              specializations = specializations.Where(s =&gt; s.Doctors.Any(d =&gt; d.Doctors_Id == doctorId));
          }
  
          return await specializations.Select(selector).ToListAsync();
  
      }
  
      public async Task&lt;ILookup&lt;string, Specializations&gt;&gt; GetSpecializationsByDoctorIdAsync(IEnumerable&lt;string&gt; doctorIds, CancellationToken token)
      {
          var specializations = await _dbContext.SpecializationsDoctors
              .Include(s =&gt; s.Specialization)
              .Where(spDocs =&gt; doctorIds.Any(sp =&gt; sp == spDocs.Doctors_Id))
              .ToListAsync();
  
          return specializations.ToLookup(i =&gt; i.Doctors_Id, i =&gt; i.Specialization);
      }
  
      public IQueryable&lt;Specializations&gt; List(string doctorId = null)
      {
          var specializations = _dbContext.Specializations.AsQueryable();
  
          if (!string.IsNullOrEmpty(doctorId))
          {
              specializations = specializations.Where(s =&gt; s.Doctors.Any(d =&gt; d.Doctors_Id == doctorId));
          }
  
          return specializations;
      }
  }
  </code></pre>
  
  <p>This post has become pretty large, sorry for the span..</p>