Entity Framework DotNet Core with GraphQL and SQL Server (using HotChocolate)

This article will cover two graphql options available for dotnet core:

Hotchocolate
Graphql-Dotnet (briefly)

So you're a dotnet developer and you've heard the hype surrounding graphql, and you think "oh yeah, I just HAVE to try this!". Well prepared to be deeply underwhelmed.

As a start, you'd probably google something along the lines of "dotnet graphql". You'd be presented with graphql-dotnet. You click on the github link, only to be presented with this:

Yikes! Then you decide to go into the releases page, and see the Version 2.4.0 release date: 15 Nov 2018... BIG YIKES.

Ok screw that. What's next?

Hotchocolate to the rescue? What the hell is that? This was the question that first came to mind when I saw a youtube video on graphql and dotnet. It's really the only option you have when it comes to GraphQL solutions in the dotnet world.

The latest official release, as of this post, is 10.3.6 which was released 7 days ago. OK! That's reassuring. The latest preview version was released < 24 hours ago - AWESOME. It's active, it's running, so lets start this.

Before I proceed, this will be just a basic overview to get started with Hotchocolate graphql, entityframework core and SQL Server. You will end up with a local server, and a graphql playground. I won't be going too in-depth with it, but hopefully you'll get a much better start than I did.

Overview

Things we will be doing:

Create the project
Setting up our Startup.cs file
Setup the database
Adding entities
Adding the DB Context
Adding the schema
Updating the Startup.cs file with our Query types and DB Context

Create the project

Things I'm going to use, and I'll assume you have them installed because I won't cover them:

VSCODE (Omnisharp + C# extensions installed)
SQL Server, or access to one
dotnet core 3.1 installed

First, we load up our trusty terminal (CMD, Bash, ZSH, whatever) create a project folder and navigate to it and create the project:

> md dotnet-gql-test
> cd dotnet-gql-test
> dotnet new web

This wil create a new empty web application. Once that's done, we install the dependencies:

> dotnet add package Microsoft.EntityFrameworkCore
> dotnet add package Microsoft.EntityFrameworkCore.SqlServer
> dotnet add package HotChocolate.AspNetCore
> dotnet add package HotChocolate.AspNetCore.Playground
> code .

This will install the above packages, and open up vscode. When you open up vscode, you may see some popups, just click install for C# extensions, wait for Omnisharp to install if it already isn't installed, and let it install any missing required assets.

So far, our csproj file should look like this:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <RootNamespace>dotnet_gql_test</RootNamespace>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="HotChocolate.AspNetCore" Version="10.3.6" />
    <PackageReference Include="HotChocolate.AspNetCore.Playground" Version="10.3.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
  </ItemGroup>

</Project>

Startup.cs

Open up the Startup.cs file, and change it to the following:

using HotChocolate;
using HotChocolate.AspNetCore;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace dotnet_gql_test
{
  public class Startup
  {

    public void ConfigureServices(IServiceCollection services)
    {
      services.AddGraphQL(
        SchemaBuilder.New()
        // AddQueryType<T>() here 
        .Create());
    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
      if (env.IsDevelopment())
          app.UseDeveloperExceptionPage();

      app
        .UseRouting()
        .UseWebSockets()
        .UseGraphQL()
        .UsePlayground();
    }    
  }
}

This boilerplate, will allow us to eventually use a GraphiQL style playground to test our queries.

The database

In our test database for this example, lets say we have a table

CREATE TABLE [Locations]
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL,
  [Code] [nvarchar](5) NOT NULL,
  [Active] [bit] NOT NULL

Lets insert some test data:

INSERT INTO [Locations]
  ([Name], [Code], [Active])
VALUES
  ("Sydney", "SYD", 1)
GO
INSERT INTO [Locations]
  ([Name], [Code], [Active])
VALUES
  ("Los Angeles", "LAX", 1)
GO

Entity

This is pretty much the same as your standard .NET EntityFramework entity you'd use. It's basically a class with the matching column names and types as the CREATE TABLE query above.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace dotnet_gql_test
{
  [Table("Locations")]
  public class Locations
  {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [Required, MaxLength(50)]
    public string Name { get; set; }

    [Required, MaxLength(5)]
    public string Code { get; set; }

    [Required]
    public bool Active { get; set; }
  }
}

Important: This is outside the scope of this article, but you can create a LocationType to complement this. You can find more information about it here: https://hotchocolate.io/docs/schema-descriptions

The DB Context

Again, it's pretty much identical to any standard DB Context in .NET Entity Framework. But here, for simplicity's sake, I'm going to add the database connection string.

using Microsoft.EntityFrameworkCore;

namespace dotnet_gql_test
{
  public class MyDbContext: DbContext
  {
    public static string DbConnectionString = "Data Source=database.url;Initial Catalog=mydatabasename;Persist Security Info=True;Connect Timeout=30;User ID=user;Password=pass";

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {}

    public DbSet<Locations> Location { get; set; }

  }
}

The Schema

Hear comes the meaty part. This is what we will have access to in our graphql playground. As a basic query, we'll return a list of Locations from our Location table, and another query that accepts an argument for a specific location code. The schema is pretty straightforward, you'll have one class that has queries, and another class that extends ObjectType and configures the fields for the query. The latter will be added to our Startup.cs file as a QueryType in the Schema definition.

First, we'll create the query class. This contains all the queries for this example.

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using HotChocolate;
using HotChocolate.Types;
using Microsoft.EntityFrameworkCore;

namespace dotnet_gql_test
{
  public class LocationQueries
  {
    // GetLocations: Return a list of all locations
    // Notice the [Service]. It's an auto hook up from HotChocolate
    public async Task<List<Locations>> GetLocations([Service] MyDbContext dbContext) =>
      await dbContext
        .Location
        .AsNoTracking()
        .OrderBy(o => o.Name)
        .ToListAsync();

    // GetLocation: Return a list of locations by location code
    public async Task<List<Locations>> GetLocation([Service] MyDbContext dbContext, string code) =>
      await dbContext
        .Location
        .AsNoTracking()
        .Where(w => w.Code == code)
        .OrderBy(o => o.Name)
        .ToListAsync();
    }
  }

Next, we create the query type.

using HotChocolate.Types;

namespace dotnet_gql_test
{
  public class LocationQueryType : ObjectType<LocationQueries>
  {
    protected override void Configure(IObjectTypeDescriptor<LocationQueries> descriptor)
    {
      base.Configure(descriptor);

      descriptor
        .Field(f => f.GetLocations(default))

      descriptor
        .Field(f => f.GetLocation(default, default))
        .Argument("code", a => a.Type<StringType>());
    }
  }
}

This lets our schema know that we have 2 queries available. GetLocations will get all locations. GetLocation will get a location by code.

Back to the Startup.cs file

Now that we have everything set up, we can update our Startup.cs file with the data context, and schema builder.

To do this, the update the ConfigureServices function with the following:

    public void ConfigureServices(IServiceCollection services)
    {
      // Add DbContext
      services
        .AddDbContext<MyDbContext>(options =>
          options.UseSqlServer(MyDbContext.DbConnectionString));

      // Add GraphQL Services
      services
        .AddDataLoaderRegistry()
        .AddGraphQL(SchemaBuilder
            .New()
            // Here, we add the LocationQueryType as a QueryType
            .AddQueryType<LocationQueryType>()
            .Create());
    }

Build and run

To build and run, type the following in your terminal:

> dotnet run
# an alternative is "dotnet watch run" - this adds a watcher when coding

Open your browser to http://localhost:5000/playground. This will display a graphiql playground. In this you should be able to successfully run the queries:

{
  locations {
    name
    code
  }
}

The above will return all locations.

{
  location(code: "lax") {
    name
  }
}

The above will return "Los angeles".

This covers the basic fundamentals of EFCore and GraphQL. It's not difficult, but documentation covering everything is quite sparse or non-existant.

If you have any questions, or you think I missed something (or worse, made typos), feel free to hit me up in the comments.

Hope this helps someone.

Blog