ASP.NET Core — Entity Framework Core with PostgreSQL Database

3 min readJun 21, 2020

A guide to using ASP.NET Core MVC Identity with PostgreSQL

PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. Wikipedia

Prerequisites

IDE’s

I will show you how to implement this project in Visual Studio 2019. But there are more IDEs out there to develop .NET applications.

Implementing the Project

Creating a new project

Open Visual Studio and choose ASP.NET Core Web Application.

Configure your new project

I named it as ASPPostgreSQL feel free to use any Project name. Then choose your Location and Create.

Choosing the template

Make sure that highlighted configurations are the same as below. Here I’m gonna use API. And hit Create.

Install Entity Framework Core & PostgreSQL

Right-click on the Project Solution & select Manage NuGet Packages for Solution

And then Install the following packages,

Npgsql.EntityFrameworkCore.PostgreSQL
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools

Configuration

After the installation, open the Startup.cs. Under ConfigureServices() you will find the following code block:

In this ConfigureServices method, we need to add PostgreSQL to the project Add your Database context (in my case APIContext) and the connection string name (ConnectionString is added in appsettings.json and we will do it later) (in my case APIConection).

To use UseNpgsql click on this,

Create a User Model

Create a folder called Models. Then create a file called User.cs and add below context to it.

Create the APIContext

Create a folder called Models and Right-click on that folder. And Add/New Item. Select Class name it APIContext.cs and Add.

Creating the Database

This command will create a database from PostgreSQL shell prompt, but you should have the appropriate privilege to create a database.

postgres=# CREATE DATABASE "ASPPostgreSQL.Db";

Adding the ConnectionStrings

Open appsettings.json. And configure it with your user id & password. In my case User Id=postgres and Password=psql.

User ID=postgres;Password=psql;Server=localhost;Port=5432;Database=ASPPostgreSQL.Db;Integrated Security=true;Pooling=true;

Database Migration

Open Package Manager Console and run, (In my case Migration name sets to InitialMigration)

PM> Add-Migration InitialMigration
PM> Update-Database

Add-Migration will create the first commit in migrations. You can find the migration in the Migrations folder under the project tree. Update-Database will update the database according to the recent models.