In the previous articles of this series, we have configured GraphQL with Employee table. Now, consider a scenario where you want to maintain multiple certifications for each employee. In this situation, we need to create one more table which is a kind of child table for Employee.
You can read the previous parts here.
- GraphQL In .NET Core Web API With Entity Framework Core - Part One
- GraphQL In .NET Core Web API With Entity Framework Core - Part Two
Execute the below script to create the Certification table with some dummy data.
CREATE TABLE [dbo].[Certification](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeId] [bigint] NULL,
[Title] [varchar](100) NULL,
[Month] [int] NULL,
[Year] [int] NULL,
[Provider] [varchar](100) NULL,
CONSTRAINT [PK_Certification] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Certification] ON
GO
INSERT [dbo].[Certification] ([Id], [EmployeeId], [Title], [Month], [Year], [Provider]) VALUES (1, 1, N'MCSD', 1, 2019, N'Microsoft')
GO
INSERT [dbo].[Certification] ([Id], [EmployeeId], [Title], [Month], [Year], [Provider]) VALUES (2, 1, N'Scrum Master', 2, 2019, N'Agile')
GO
INSERT [dbo].[Certification] ([Id], [EmployeeId], [Title], [Month], [Year], [Provider]) VALUES (3, 2, N'MCT', 12, 2018, N'Microsoft')
GO
INSERT [dbo].[Certification] ([Id], [EmployeeId], [Title], [Month], [Year], [Provider]) VALUES (4, 2, N'PMP', 1, 2019, N'PMP')
GO
SET IDENTITY_INSERT [dbo].[Certification] OFF
GO
Execute the Scaffold command in Package Manager Console to update the model and dbContext with Certification table.
Scaffold-DbContext “Server=AKSHAY-PC\DEVSQL2016;Database=GraphQLDemo;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -force
Post execution, you will be able to see that Certification model is created and DbSet is added for the same in dbContext.
namespace GraphQLInWebApiCore
{
public partial class Certification
{
public long Id { get; set; }
public long EmployeeId { get; set; }
public string Title { get; set; }
public int? Month { get; set; }
public int? Year { get; set; }
public string Provider { get; set; }
}
}
public virtual DbSet<Certification> Certification { get; set; }
In the previous example, we created an EmployeeType. Similarly, we need to create a EmployeeCertificationType, which inherits ObjectGraphType.
EmployeeCertificationType.cs
using GraphQL.Types;
namespace GraphQLInWebApiCore
{
public class EmployeeCertificationType : ObjectGraphType<Certification>
{
public EmployeeCertificationType()
{
Field(t => t.Id);
Field(t => t.Title);
Field(t => t.Month, nullable: true);
Field(t => t.Year, nullable: true);
Field(t => t.Provider);
}
}
}
Create a repository to fetch certificates by employeeId. For that, we need to add ICertificationRepository and CertificationRepository.
using System.Collections.Generic;
using System.Threading.Tasks;
namespace GraphQLInWebApiCore
{
public interface ICertificaationRepository
{
Task<List<Certification>> GetCertificationByEmployee(long EmployeeId);
}
}
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace GraphQLInWebApiCore
{
public class CertificationRepository : ICertificaationRepository
{
private readonly GraphQLDemoContext _context;
public CertificationRepository(GraphQLDemoContext context)
{
_context = context;
}
public Task<List<Certification>> GetCertificationByEmployee(long EmployeeId)
{
return _context.Certification.Where(a => a.Id == EmployeeId).ToListAsync();
}
}
}
Add the below line of code under ConfigureServices in Startup.cs so that it will be added in service collection and later, injected as and when required.
services.AddScoped<ICertificaationRepository, CertificationRepository>();
As we know, it’s a parent child relationship; i.e., one employee can have multiple certifications. We need to add ListGraphType of EmployeeCertificationType in EmployeeType. So, inject CertificationRepository in EmployeeType constructor.
Add a field of type ListGraphType of EmployeeCertificationType and resolve the context by calling GetCertificationByEmployee method. Here, we need to pass the employeeId as parameter value.
public EmployeeType(ICertificationRepository certificationRepository)
{
….
}
using GraphQL.Types;
namespace GraphQLInWebApiCore
{
public class EmployeeType : ObjectGraphType<Employee>
{
public EmployeeType(ICertificationRepository certificationRepository)
{
…
Field<ListGraphType<EmployeeCertificationType>>(
"certifications",
resolve: context => certificationRepository.GetCertificationByEmployee(context.Source.Id)
);
}
}
}
Run the application and write the below query.
{
employees{
name
certifications{
title
}
}
}
In the above query, you can observe that we have added certifications as one of the fields of employees and we get the result from multiple tables.
Let’s expose the same thing from the Web API.
Add GraphQL.Client NuGet package.
In the existing Get method of EmployeeController, just change the query. You can copy and paste the query which we have executed in playground UI.
using GraphQL.Client;
using GraphQL.Common.Request;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace GraphQLGraphTypeFirstNestedTable.Controllers
{
[Route("Employee")]
public class EmployeeController : Controller
{
[HttpGet]
public async Task<List<Employee>> Get()
{
using (GraphQLClient graphQLClient = new GraphQLClient("http://localhost:64350/graphql"))
{
var query = new GraphQLRequest
{
Query = @"
{ employees
{ name
email
certifications
{ title }
}
}",
};
var response = await graphQLClient.PostAsync(query);
return response.GetDataFieldAs<List<Employee>>("employees");
}
}
}
}
As we are exposing Employee model from API, we need to add List of Certification as one of the properties in Employee
using System.Collections.Generic;
namespace GraphQLGraphTypeFirstNestedTable
{
public partial class Employee
{
…
public List<Certification> certifications { get; set; }
}
}
Run the application, copy the URL from the browser, append with /Employee and click on the "Send" button.
You can see all the employees with their certifications.
This approach is for understanding purposes only. In real application development, we should use DataLoader to load certifications data. Ideally, it’s not a good practice to load one by one for each request. We will see the DataLoader stuff in the next article.
You can download the sample from here.