New .NET 6 Project

Work in progress...

This guide helps you setup a new .NET 6 project using PostgreSQL as a database.

Scaffold a project

dotnet new webapi -o myapp
cd myapp
dotnet new gitignore

Configure an existing project using SQL Server to PostgreSQL

This scenario assumes that you used EntityFramework and have existing models and configuration for SQL Server.

Setup Entity Framework and PostgreSQL libraries

dotnet add package Npgsql
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design

In appsettings.json add a connection string for PostgreSQL.

"ConnectionStrings": {
    "DefaultConnection": "you_can_retain_existing_connection",
    "PgConnection": "Host=localhost;Database=mydb;Username=myuser;Password=mypassword"
  }

In Program.cs setup the DbContext

var connectionString = builder.Configuration.GetConnectionString("PgConnection");
builder.Services.AddDbContext<MYDB>(opt => opt.UseNpgsql(connectionString));

Run the migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

Create Models

:WIP:

Reverse Engineering an Existing Database

This scenario is when you have an existing database structure. You can generate the Models automatically by running one of the following scaffolding commands. The --output-dir parameter sets the location of the generated files. If you don't specify this parameter, it will create the files in the root folder.

https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli

-- SQL Server source
dotnet ef dbcontext scaffold "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Chinook" Microsoft.EntityFrameworkCore.SqlServer" --output-dir Models

-- PostgreSQL source
dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL --output-dir Models