Creating a .Net Core REST API — Part 1: Setup and Database Modelling

I’ll be putting together a series of tutorials on creating a REST API with ASP.Net Core and Entity Framework Core against a Postgres database. Microsoft has some great tutorials as well but they weren’t always fully applicable to my use-case of developing an API against an existing database schema.

Anything in this tutorial should be possible on other platforms however I’ll be developing on Windows 10 with Visual Studio Community 2017.

Project Setup

ASP.Net Core has a web application template that can help get us started. When creating your project be sure to select it.

After that select the plain API template.

You should end up with a simple API that returns some hardcoded values. The default configuration should run the API on IIS Express and launch a browser pointing to the API endpoint. You can also use a tool like Postman to test the API. Just be aware that closing the browser window will stop the debugging session.

Database Modelling

Our API will run queries against our database using Entity Framework Core. EF Core supports two models of development; Code First and Database First. Code first generates the database from models that you write manually. Database first is the opposite, generating the models from an existing database schema. We will be using database first in this case.

In order to model and query our Postgres database EF Core requires a Database Provider to be installed. We will install Npgsql which is an open source Postgres provider. You can add it to your project from NuGet using the Visual Studio GUI or the command line.

For the following steps you will need a Postgres database running. You can run one locally on Windows or on another server. I setup Postgres on an Ubuntu server, created a database, and then allowed external connections. Once you have one running we can create a simple schema by running the following SQL script:

You may notice I use plural table names in the database. If you want the model classes to have singular names you can install a pluralizer package to do this when the model is generated.

To model this schema we can run one of the following commands in command prompt, WSL, PowerShell, or the VS Package Manager Console. Make sure you are in the directory containing the project file (*.csproj).

See the command line reference for more options.

Once the modelling is complete you will have 3 classes in the Models directory and one in the Context directory. The Models each represent a row in the database and it’s related data while the DataContext represents the full state of the database.

If you build the project you may notice a warning from a #warning directive in the OnConfiguring handler of BlogContext. EF Core embeds the connection string we used for modeling into the DataContext so that it will work for database access out of the box. This represents a potential security risk so you can remove the entire OnConfiguring handler and we will configure the connection string next time.

The source code for this tutorial is available on GitHub. Up next we’ll start setting up our API controller.