mnsr

Posted on Mar 6, 2020

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

#dotnet #graphql #efcore #mssql

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.

Oldest comments (15)

Todd-Fulton • Mar 11 '20

Thanks, been trying to figure out how to hook up a database to hotchocolate for a couple days. Looks like instead of hc, it's handled by asp.net and ef core, which I know next to nothing about. Lol, was reading Hotchocolate's - Tutorial Mongo where they say:

"You are all fired up and want to get started with a little tutorial walking you through an end-to-end example with MongoDB as your database?"

...but nothing about actually hooking up to mongodb, as of yet (facepalm). From reading this my next guess would be to combine info from that tut with Create a web API with ASP.NET Core and MongoDB somehow, and then implementing the schema and resolvers and such instead of a rest api. Lots to learn.

Thanks again.

Michael Staib • Mar 11 '20

That tutorial for mongo was not supposed to be published on the documentation. It kind of is not finished and stops mid-sentence.

Here is a good example of how you can do mongo with hc:
github.com/ChilliCream/graphql-wor...

Also we have an open slack channel where we help people along:
join.slack.com/t/hotchocolategraph...

However, version 10.4 is soon out and will bring support for projections and with that makes it even easier to bind your database to hc.

PascalSenn • Mar 11 '20

Regarding projections:

Given this Query type we generate all the nested types you need. By annotating with attributes you can add additional behavior on top of it.

public class Query {
     [UsePaging]
     [UseSelection] // <--- creates the projections on the database
     [UseSorting]
     [UseFiltering]
     public IQueryable<Jedi> GetJedis([Service]DBContext ctx) 
       => ctx.Jedis.AsQueryable()

}

When you now execute this query

{
  jedis(where: {name: "Han"}, order_by:{side:DESC}) { name side }
}

This SQL statement is executed:

SELECT [p].[Name],[p].[Side]
FROM [Jedis] AS [p]
WHERE [p].[Name] ="Han"
ORDER BY [p].[Side] DESC

Todd-Fulton • Mar 11 '20

Thank you for info and redirect to the repo.

I actually got it working with the BookAPI example on the ms site, and the startwars example repo. I basically just followed the BookAPI tutorial until they started talking about controllers, then looked at the starwars repo and implemented a resolver that used the BookService that was connected to mongo, instead of the in-memory repository, and then implemented the types for the book and query and hooked it all up in startup.

I'll go through the workshop repo and see how it compares to what I did to see if I was on the right track, and start learning about all the other stuff.

Thank you kindly!

PascalSenn • Mar 14 '20

Join the slack channel if you have any questions, you get usually an answer real quickly :)

Andrés Pérez • Mar 11 '20

I'm still looking for some resource on how to approach relationships in entity. Last time I asked they said it was a WIP :(

Michael Staib • Mar 12 '20

That is what pascal actually posted up there:

public class Query {
[UsePaging]
[UseSelection] // <--- creates the projections on the database
[UseSorting]
[UseFiltering]
public IQueryable GetJedis([Service]DBContext ctx)
=> ctx.Jedis.AsQueryable()

}

You put our new selections middleware on the resolver and then basically the whole subtree is selectable in one go.

Try out our preview for 10.4

You only need to declare that on the root resolver and hc will allow full selection of the whole tree.

Chris Noring • Mar 13 '20 • Edited

I would love to use HotChocolate, API looks amazing but API is very changed per 10.3.6. There's no way you can follow the docs even for hello world.. Right now I'm actively reading the source code instead of relying on the docs.. Give this lib a few months to get things in order - this is not ready for production !! If you use this tutorial for example, you will get up and running hotchocolate.io/docs/tutorial-01-g... but if you look at this page you get stuck straight away as the docs aren't updated, hotchocolate.io/docs/introduction . That's not a great look on an intro page

Michael Staib • Mar 14 '20

That is not entirely true the API has not changed and you just have to add using HotChocolate.Execution; to get the extension methods.

I also answered this in your issue.
github.com/ChilliCream/hotchocolat...

Regarding production ready. We are. HotChocolate is the most spec compliant and the fastest GraphQL library for .NET. If you run just the parser kitchen sink tests from Facebook GraphQL-DotNet crashes but Hot Chocolate will run each of them.

Also it is the most feature rich GraphQL Platform for .NET. We have a very active support channel which solves problems of their users very quickly. If you post a question to us we answer within 12 hours most of the times immediately.

Further, apart from that we have a far bigger platform with tools like Banana Cake Pop (GraphQL IDE) or our .NET clients.

We will add to our documentation that you have to add this using. I thought personally that this is not necessary since most Dev IDEs will auto import those. It is basically the same like with Microsofts ServiceCollection many methods are extension methods that have to be imported.

Chris Noring • Mar 14 '20

hi Michael. I'm on VS Code.. there is no auto import happening. That's what a lot of devs use if on Mac or Linux. Please add the needed usings for all your examples or add a link to a sample project on GitHub where I can figure this out. I appreciate you taking the time to answer. As I said it looks like a great API, great features but I'm sure you understand how frustrating it is to get stuck

Michael Staib • Mar 14 '20

I do get that. We are working hard on out new documentation that we will release alongside the 10.4 release coming next week. I will add the usings to the examples and we will have blog posts going up with the 10.4 release showing how to use it with entity framework in more depth.

Hot Chocolate is developed on macOS and VSCode. That is why I thought that any other IDE would do the same auto import when pressing command + . on the missing method. But I agree that is not actually that obvious. Be assured that we will update the docs over the next week.

Loren Koss • Apr 3 '20

Great article! I recommend changing your AddDbContext to make the dbcontext with Transient scpe like this:
services
.AddDbContext(options =>
options.UseSqlServer(MyDbContext.DbConnectionString), ServiceLifetime.Transient);

With GraphQL, many queries could happen simultaneously and you will find that queries in one dbcontext cant be run at the same time.

BossJake • Jun 13 '20 • Edited

Hey Michael, I'm new to GraphQL hot chocoloate but this is really a good example and an easy tutorial to follow. Thank you!.

Question #1: In your example, you use "code" as a parameter for the LocationQueries.GetLocation(). If I want to use Id or Active, how do we implement this?

Can we do it like GetLocationByCode or GetLocationByActive. Is this even the right approach? I've done it this way. Is this correct?

dev-to-uploads.s3.amazonaws.com/i/...

Question #2: Do you have an example on how we can query or get the data from a Rest API?
i.e. I want to fetch the data from an http rest api and expose it in the Location Query Type.

aahock • Jun 25 '20

Thanks mnse. That was a big help. I also had the EXACT same thoughts today as I was researching where to find a graphql library for .net 3.1. 2018? Uh, no thanks. Next!

Seems like Hot Chocolate is the only game in town. Hope it works. Whoever came up w/the name Hot Chocolate for a GraphQL .NET library should see a psychiatrist. Immediately. And take a marketing course.

LeTrung • Jul 8 '20

I am working on version 10.4.3 and I am following this document for Subscriptions but might it out of date because IEventMessage, IEventSender is obsoleted, do you have any new instruction for Subscriptions?
hotchocolate.io/docs/code-first-su...

DEV Community

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

Overview

Create the project

Startup.cs

The database

Entity

The DB Context

The Schema

Back to the Startup.cs file

Build and run

Oldest comments (15)

Read next

ABP Suite: Best CRUD Page Generation Tool for .NET

Desvendando o Poder do Docker, Nginx e dotnet 8: Configurando um Proxy Reverso com Facilidade

React-Relay 2 years later , It's still awesome

How To Easily Make Your .NET AWS Lambda Function Idempotent