In the previous articles of this series, we have configured GraphQL with an 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 Employees.
You can read the previous parts here.
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 the Package Manager Console to update the model and dbContext with the 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 the 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 an 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 the employee. 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 to 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 the EmployeeType constructor.
Add a field of type ListGraphType of EmployeeCertificationType and resolve the context by calling the GetCertificationByEmployee method. Here, we need to pass the employeeId as the parameter value.
using GraphQL.Types;
namespace GraphQLInWebApiCore
{
public class EmployeeType : ObjectGraphType<Employee>
{
public EmployeeType(ICertificationRepository certificationRepository)
{
// Your code here
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.
![Entity Framework]()
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 that we have executed in the 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 the Employee model from API, we need to add the List of Certification as one of the properties in the 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.
![Certifications]()
This approach is for understanding purposes only. In real application development, we should use DataLoader to load certification 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.