GraphQL in ASP.NET Core with Entity Framework and Hot Chocolate
Sachin Nandanwar
Few months ago I had published an introductory article on GraphQL in Azure Functions. You can find that article here.I will strongly recommend to go through that article to make yourself familiar with basics of GraphQL so that its easy to follow and understand the steps involved in this article.
In this article we will see how to expose data via GraphQL API endpoint through HotChocolate in ASP .NET core with data fetched from an Azure SQL Database through Entity Framework .
I will use two tables Sales.SalesOrderHeader and Sales.SalesOrderDetails from the AdventureWorks database. The joining key across the two tables is the SalesId key.
SetUp
First, create a blank ASP .NET core Project.
Add the following Nuget Packages to the project
dotnet add package Microsoft.EntityFrameworkCore --version 9.0.2
dotnet add package HotChocolate.AspNetCore --version 12.0.0
dotnet add package Microsoft.Data.SqlClient --version 5.0.0
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 9.0.2
dotnet add package Microsoft.EntityFrameworkCore.Tools --version 9.0.2
Add four new folders :
Models » To hold models of the underlying tables
OrderTypes » To hold classes that defines GraphQL types. More details on Types in GraphQL here.
Query » To hold Query classes. More details on GraphQL Query here.
DbContext » To hold the DbContext class
The project folder structure should look like this
Once done, we will use ASP .NET Scaffolding method to reverse engineer the source tables and generate the corresponding models for our code.
Scaffold-DbContext "Data Source=##########;Initial Catalog=Adventureworks;User ID=******;Password=******" Microsoft.EntityFrameworkCore.SqlServer -Tables Sales.SalesOrderHeader,Sales.SalesOrderDetail
Note : That I have created the Model classes specifically for tables SalesOrderHeader and SalesOrderDetails throught the-Tables option. This creates a DBContext and models classes for SalesOrderHeader and SalesOrderDetails.
A DBContextclass file is generated with the default database name. You can keep the name as is or rename it. I have renamed it to SalesOrdersDbContext
Remove the OnConfiguring method from the DBContextclass and move the connection string to the appsettings.json file of the project.
The DbContext file looks something like this
using Microsoft.EntityFrameworkCore;
using SalesOrderHeader = GraphQLApp.Models.SalesOrderHeader;
using SalesOrderDetail = GraphQLApp.Models.SalesOrderDetail;
namespace GraphQLApp.DatabaseContext;
public partial class SalesOrdersDbContext : DbContext
{
public SalesOrdersDbContext()
{
}
public SalesOrdersDbContext(DbContextOptions<SalesOrdersDbContext> options)
: base(options)
{
}
public virtual DbSet<SalesOrderDetail> SalesOrderDetails { get; set; }
public virtual DbSet<SalesOrderHeader> SalesOrderHeaders { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SalesOrderDetail>(entity =>
{
entity.HasKey(e => new { e.SalesOrderId, e.SalesOrderDetailId }).HasName("PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID");
entity.ToTable("SalesOrderDetail", "Sales", tb =>
{
tb.HasComment("Individual products associated with a specific sales order. See SalesOrderHeader.");
tb.HasTrigger("iduSalesOrderDetail");
});
entity.HasIndex(e => e.Rowguid, "AK_SalesOrderDetail_rowguid").IsUnique();
entity.HasIndex(e => e.ProductId, "IX_SalesOrderDetail_ProductID");
entity.Property(e => e.SalesOrderId)
.HasComment("Primary key. Foreign key to SalesOrderHeader.SalesOrderID.")
.HasColumnName("SalesOrderID");
entity.Property(e => e.SalesOrderDetailId)
.ValueGeneratedOnAdd()
.HasComment("Primary key. One incremental unique number per product sold.")
.HasColumnName("SalesOrderDetailID");
entity.Property(e => e.CarrierTrackingNumber)
.HasMaxLength(25)
.HasComment("Shipment tracking number supplied by the shipper.");
entity.Property(e => e.LineTotal)
.HasComputedColumnSql("(isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0)))", false)
.HasComment("Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.")
.HasColumnType("numeric(38, 6)");
entity.Property(e => e.ModifiedDate)
.HasDefaultValueSql("(getdate())")
.HasComment("Date and time the record was last updated.")
.HasColumnType("datetime");
entity.Property(e => e.OrderQty).HasComment("Quantity ordered per product.");
entity.Property(e => e.Rowguid)
.HasDefaultValueSql("(newid())")
.HasComment("ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.")
.HasColumnName("rowguid");
entity.Property(e => e.UnitPrice)
.HasComment("Selling price of a single product.")
.HasColumnType("money");
entity.Property(e => e.UnitPriceDiscount)
.HasComment("Discount amount.")
.HasColumnType("money");
});
modelBuilder.Entity<SalesOrderHeader>(entity =>
{
entity.HasKey(e => e.SalesOrderId).HasName("PK_SalesOrderHeader_SalesOrderID");
entity.ToTable("SalesOrderHeader", "Sales", tb =>
{
tb.HasComment("General sales order information.");
tb.HasTrigger("uSalesOrderHeader");
});
entity.HasIndex(e => e.SalesOrderNumber, "AK_SalesOrderHeader_SalesOrderNumber").IsUnique();
entity.HasIndex(e => e.Rowguid, "AK_SalesOrderHeader_rowguid").IsUnique();
entity.HasIndex(e => e.CustomerId, "IX_SalesOrderHeader_CustomerID");
entity.HasIndex(e => e.SalesPersonId, "IX_SalesOrderHeader_SalesPersonID");
entity.Property(e => e.SalesOrderId)
.HasComment("Primary key.")
.HasColumnName("SalesOrderID");
entity.Property(e => e.AccountNumber)
.HasMaxLength(15)
.HasComment("Financial accounting number reference.");
entity.Property(e => e.Comment)
.HasMaxLength(128)
.HasComment("Sales representative comments.");
entity.Property(e => e.CreditCardApprovalCode)
.HasMaxLength(15)
.IsUnicode(false)
.HasComment("Approval code provided by the credit card company.");
entity.Property(e => e.DueDate)
.HasComment("Date the order is due to the customer.")
.HasColumnType("datetime");
entity.Property(e => e.Freight)
.HasComment("Shipping cost.")
.HasColumnType("money");
entity.Property(e => e.ModifiedDate)
.HasDefaultValueSql("(getdate())")
.HasComment("Date and time the record was last updated.")
.HasColumnType("datetime");
entity.Property(e => e.OnlineOrderFlag)
.HasDefaultValue(true)
.HasComment("0 = Order placed by sales person. 1 = Order placed online by customer.");
entity.Property(e => e.OrderDate)
.HasDefaultValueSql("(getdate())")
.HasComment("Dates the sales order was created.")
.HasColumnType("datetime");
entity.Property(e => e.PurchaseOrderNumber)
.HasMaxLength(25)
.HasComment("Customer purchase order number reference. ");
entity.Property(e => e.RevisionNumber).HasComment("Incremental number to track changes to the sales order over time.");
entity.Property(e => e.Rowguid)
.HasDefaultValueSql("(newid())")
.HasComment("ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.")
.HasColumnName("rowguid");
entity.Property(e => e.SalesOrderNumber)
.HasMaxLength(25)
.HasComputedColumnSql("(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***'))", false)
.HasComment("Unique sales order identification number.");
entity.Property(e => e.ShipDate)
.HasComment("Date the order was shipped to the customer.")
.HasColumnType("datetime");
entity.Property(e => e.Status)
.HasDefaultValue((byte)1)
.HasComment("Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled");
entity.Property(e => e.SubTotal)
.HasComment("Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.")
.HasColumnType("money");
entity.Property(e => e.TaxAmt)
.HasComment("Tax amount.")
.HasColumnType("money");
entity.Property(e => e.TotalDue)
.HasComputedColumnSql("(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))", false)
.HasComment("Total due from customer. Computed as Subtotal + TaxAmt + Freight.")
.HasColumnType("money");
});
modelBuilder.Entity<SalesOrderDetail>()
.HasOne(od => od.SalesOrderHeader)
.WithMany(oh => oh.SalesOrderDetails)
.HasForeignKey(od => od.SalesOrderId);
modelBuilder.Entity<SalesOrderDetail>()
.HasKey(od => new { od.SalesOrderId, od.SalesOrderDetailId });
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
Note: I have explicitly defined relationship across the models SalesOrderHeader and SalesOrderDetails through SalesOrderId key in the context file.
modelBuilder.Entity<SalesOrderDetail>()
.HasOne(od => od.SalesOrderHeader)
.WithMany(oh => oh.SalesOrderDetails)
.HasForeignKey(od => od.SalesOrderId);
modelBuilder.Entity<SalesOrderDetail>()
.HasKey(od => new { od.SalesOrderId, od.SalesOrderDetailId });
Below is the auto-generated SalesOrderHeader model file generated through scaffolding
using System.ComponentModel.DataAnnotations;
namespace GraphQLApp.Models
{
public class SalesOrderHeader
{
[Key]
public int SalesOrderId { get; set; }
/// <summary>
/// Incremental number to track changes to the sales order over time.
/// </summary>
public byte RevisionNumber { get; set; }
/// <summary>
/// Dates the sales order was created.
/// </summary>
public DateTime OrderDate { get; set; }
/// <summary>
/// Date the order is due to the customer.
/// </summary>
public DateTime DueDate { get; set; }
/// <summary>
/// Date the order was shipped to the customer.
/// </summary>
public DateTime? ShipDate { get; set; }
/// <summary>
/// Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
/// </summary>
public byte Status { get; set; }
/// <summary>
/// 0 = Order placed by sales person. 1 = Order placed online by customer.
/// </summary>
public bool OnlineOrderFlag { get; set; }
/// <summary>
/// Unique sales order identification number.
/// </summary>
public string SalesOrderNumber { get; set; } = null!;
/// <summary>
/// Customer purchase order number reference.
/// </summary>
public string? PurchaseOrderNumber { get; set; }
/// <summary>
/// Financial accounting number reference.
/// </summary>
public string? AccountNumber { get; set; }
/// <summary>
/// Customer identification number. Foreign key to Customer.BusinessEntityID.
/// </summary>
public int CustomerId { get; set; }
/// <summary>
/// Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.
/// </summary>
public int? SalesPersonId { get; set; }
/// <summary>
/// Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
/// </summary>
public int? TerritoryId { get; set; }
/// <summary>
/// Customer billing address. Foreign key to Address.AddressID.
/// </summary>
public int BillToAddressId { get; set; }
/// <summary>
/// Customer shipping address. Foreign key to Address.AddressID.
/// </summary>
public int ShipToAddressId { get; set; }
/// <summary>
/// Shipping method. Foreign key to ShipMethod.ShipMethodID.
/// </summary>
public int ShipMethodId { get; set; }
/// <summary>
/// Credit card identification number. Foreign key to CreditCard.CreditCardID.
/// </summary>
public int? CreditCardId { get; set; }
/// <summary>
/// Approval code provided by the credit card company.
/// </summary>
public string? CreditCardApprovalCode { get; set; }
/// <summary>
/// Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
/// </summary>
public int? CurrencyRateId { get; set; }
/// <summary>
/// Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
/// </summary>
public decimal SubTotal { get; set; }
/// <summary>
/// Tax amount.
/// </summary>
public decimal TaxAmt { get; set; }
/// <summary>
/// Shipping cost.
/// </summary>
public decimal Freight { get; set; }
/// <summary>
/// Total due from customer. Computed as Subtotal + TaxAmt + Freight.
/// </summary>
public decimal TotalDue { get; set; }
/// <summary>
/// Sales representative comments.
/// </summary>
public string? Comment { get; set; }
/// <summary>
/// ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
/// </summary>
public Guid Rowguid { get; set; }
/// <summary>
/// Date and time the record was last updated.
/// </summary>
public DateTime ModifiedDate { get; set; }
public ICollection<SalesOrderDetail> SalesOrderDetails { get; set; }
}
}
Explicitly add an additional property public ICollection<SalesOrderDetail> SalesOrderDetails { get; set; } to the SalesOrderHeader class.
Below is the the auto-generated SalesOrderDetail model generated through scaffolding
using System.ComponentModel.DataAnnotations;
namespace GraphQLApp.Models;
/// <summary>
/// Individual products associated with a specific sales order. See SalesOrderHeader.
/// </summary>
public class SalesOrderDetail
{
/// <summary>
/// Primary key. Foreign key to SalesOrderHeader.SalesOrderID.
/// </summary>
public int SalesOrderId { get; set; }
/// <summary>
/// Primary key. One incremental unique number per product sold.
/// </summary>
public int SalesOrderDetailId { get; set; }
/// <summary>
/// Shipment tracking number supplied by the shipper.
/// </summary>
public string? CarrierTrackingNumber { get; set; }
/// <summary>
/// Quantity ordered per product.
/// </summary>
public short OrderQty { get; set; }
/// <summary>
/// Product sold to customer. Foreign key to Product.ProductID.
/// </summary>
public int ProductId { get; set; }
/// <summary>
/// Promotional code. Foreign key to SpecialOffer.SpecialOfferID.
/// </summary>
public int SpecialOfferId { get; set; }
/// <summary>
/// Selling price of a single product.
/// </summary>
public decimal UnitPrice { get; set; }
/// <summary>
/// Discount amount.
/// </summary>
public decimal UnitPriceDiscount { get; set; }
/// <summary>
/// Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
/// </summary>
public decimal LineTotal { get; set; }
/// <summary>
/// ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
/// </summary>
public Guid Rowguid { get; set; }
/// <summary>
/// Date and time the record was last updated.
/// </summary>
public DateTime ModifiedDate { get; set; }
public SalesOrderHeader SalesOrderHeader { get; set; }
}
In the SalesOrderDetail model, explicitly add an additional property public SalesOrderHeader SalesOrderHeader { get; set; } .
Move both the classes to the Modelsfolder of the project.
Code
Now that we have the Model and DbContext ready, we move on the next step to define GraphQL Query and Types.
Add a new class file in the OrderTypes folder and define the desired fields to be queried on. I am selecting only a few columns from the model.
using GraphQLApp.Models;
namespace GraphQLApp.OrderTypes
{
public class SalesOrderHeaderType : ObjectType<SalesOrderHeader>
{
protected override void Configure(IObjectTypeDescriptor<SalesOrderHeader> descriptor)
{
descriptor.BindFieldsExplicitly();
descriptor.Field(oh => oh.SalesOrderId).Type<NonNullType<IntType>>();
descriptor.Field(oh => oh.PurchaseOrderNumber).Type<StringType>();
descriptor.Field(oh => oh.SalesOrderNumber).Type<NonNullType<StringType>>();
descriptor.Field(oh => oh.RevisionNumber).Type<NonNullType<StringType>>();
descriptor.Field(oh => oh.OrderDate).Type<NonNullType<DateTimeType>>();
descriptor.Field(oh => oh.DueDate).Type<NonNullType<DateTimeType>>();
descriptor.Field(oh => oh.ShipDate).Type<NonNullType<DateTimeType>>();
descriptor.Field(oh => oh.TaxAmt).Type<NonNullType<DecimalType>>();
descriptor.Field(oh => oh.TotalDue).Type<NonNullType<DecimalType>>();
descriptor.Field(oh => oh.Status).Type<NonNullType<DateTimeType>>();
descriptor.Field(oh => oh.SalesOrderDetails).Type<ListType<SalesOrderDetailType>>();
}
}
public class SalesOrderDetailType : ObjectType<SalesOrderDetail>
{
protected override void Configure(IObjectTypeDescriptor<SalesOrderDetail> descriptor)
{
descriptor.BindFieldsExplicitly();
descriptor.Field(od => od.SalesOrderDetailId).Type<NonNullType<IntType>>();
descriptor.Field(od => od.SalesOrderId).Type<NonNullType<IntType>>();
descriptor.Field(od => od.CarrierTrackingNumber).Type<StringType>();
descriptor.Field(od => od.OrderQty).Type<NonNullType<FloatType>>();
descriptor.Field(od => od.UnitPrice).Type<NonNullType<FloatType>>();
descriptor.Field(od => od.UnitPriceDiscount).Type<NonNullType<FloatType>>();
descriptor.Field(od => od.LineTotal).Type<NonNullType<FloatType>>();
// descriptor.Field(od => od.SalesOrderHeader).Type<SalesOrderHeaderType>();
}
}
}
The Configure method in above classes basically overrides the base class method of SalesOrderHeader and SalesOrderDetail. It defines how the SalesOrderHeaderand SalesOrderDetail object will be exposed through the GraphQL API.
descriptor.BindFieldsExplicitly() explicitly binds the fields instead of HotChocolate inferring the columns. The rest of the lines descriptor.Field(oh => oh.PropertyName) defines the fields for the GraphQL queries.
Note : The line descriptor.Field(oh => oh.SalesOrderDetails).Type<ListType<SalesOrderDetailType>>(); in the SalesOrderHeader descriptor type, returns all the related records for SalesOrderDetails.
Now you could do the same with SalesOrderDetail model using a type definition like below.
descriptor.Field(od => od.SalesOrderHeader).Type<SalesOrderHeaderType>();
But this will lead to circular reference as SalesOrderHeader will reference SalesOrderDetailswhich in turn will again reference SalesOrderHeader and so on..
In the next step, we define the Query Type for the models which provides the read only view
using GraphQLApp.DatabaseContext;
using GraphQLApp.Models;
using Microsoft.EntityFrameworkCore;
namespace GraphQLApp.Query
{
public class SalesOrderQuery
{
public IQueryable<SalesOrderHeader> GetOrderHeaders([Service] SalesOrdersDbContext dbContext) =>
dbContext.SalesOrderHeaders.Include(oh => oh.SalesOrderDetails);
}
}
Note : We are using the IQueryable<T> interface and injecting SalesOrdersDbContext into the method through Service. HotChocolate uses middleware to apply filter to IQueryable<T> to fetch the data through Entity framework.
In the next step add the following code to Program.cs
using GraphQLApp.DatabaseContext;
using GraphQLApp.OrderTypes;
using Microsoft.EntityFrameworkCore;
using SalesOrderQuery = GraphQLApp.Query.SalesOrderQuery;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<SalesOrdersDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddGraphQLServer()
.AddQueryType<SalesOrderQuery>()
.AddType<SalesOrderHeaderType>()
.AddType<SalesOrderDetailType>();
var app = builder.Build();
app.MapGraphQL();
app.Run();
In Program.cs, we are adding a query type SalesOrderQuery and registering SalesOrderHeaderType and SalesOrderDetailType .Through Dependency Injection(DI) registering theSalesOrdersDbContext class.
The launchSettings.json file
{
"profiles": {
"http": {
"commandName": "Project",
"launchBrowser": true,
"launchUrl": "graphql",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
},
"dotnetRunMessages": true,
"applicationUrl": "http://localhost:5079"
}
},
"$schema": "https://json.schemastore.org/launchsettings.json",
"iisSettings": {
"windowsAuthentication": false,
"anonymousAuthentication": true,
"iisExpress": {
"applicationUrl": "http://localhost:52717",
"sslPort": 44327
}
}
}
The applicationUrl is http://localhost:5079 with launchUrl is graphql.
That’s all. Just go ahead and execute the project and play around in the Banana cake pop IDE of HotChocolate.
In an upcoming article, I will expand on this article by implementing Mutations, Paging, Filtering and Sorting.
Thank you reading !!!
Subscribe to my newsletter
Read articles from Sachin Nandanwar directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by