PostgreSQL with .NET and EF Core — and Your Migration Plan

Previously in Part 7, we built a safety net with pg_dump, base backups, and WAL archiving. Time for the payoff: wiring PostgreSQL into .NET, and the plan for actually moving a real application across.

Connecting from .NET: Npgsql

Where SQL Server has Microsoft.Data.SqlClient, PostgreSQL has Npgsql — and it is not a second-class citizen. Npgsql is a mature, actively maintained ADO.NET provider that the .NET team itself uses in benchmarks. Same shapes you already know: a connection, a command, a reader, async everywhere.

The connection string is the first visible difference:

# SQL Server
Server=.;Database=ShopDb;Integrated Security=true;TrustServerCertificate=true

# PostgreSQL (Npgsql)
Host=localhost;Database=shopdb;Username=app;Password=secret

The keywords change (Server becomes Host), and Windows-integrated security gives way to username/password or certificate authentication — in production you will typically pull that password from configuration or a secrets store, exactly as you do today.

EF Core: a one-line swap (almost)

EF Core's provider model is where your migration gets pleasantly boring. Install Npgsql.EntityFrameworkCore.PostgreSQL, change UseSqlServer to UseNpgsql, and most of your application compiles and runs:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package EFCore.NamingConventions

builder.Services.AddDbContext<ShopContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Default"))
           .UseSnakeCaseNamingConvention());

What actually changes underneath:

Naming. As we saw in Part 2, unquoted identifiers fold to lowercase in PostgreSQL, so idiomatic schemas use snake_case. The EFCore.NamingConventions package's UseSnakeCaseNamingConvention() maps your OrderItem class to an order_items table automatically — no entity changes needed.
Migrations regenerate. EF migrations contain provider-specific SQL, so you delete the old ones and run a fresh dotnet ef migrations add Initial against the new provider. Your model classes stay untouched.
LINQ mostly just works. The provider translates the same expression trees to PostgreSQL SQL. The place to be careful is raw SQL — anything in FromSqlRaw or hand-written queries needs the Part 4 treatment.

Tip: Using Dapper or plain ADO.NET? Your parameter habits survive intact. Npgsql supports the @p parameter syntax you have used for years, so "SELECT id, total FROM orders WHERE id = @id" with new { id = 42 } works exactly as before. Only the SQL dialect itself needs translating, not your data-access plumbing.

The migration plan

Here is the whole series folded into one checklist. This is the order that works — resist the temptation to jump straight to step 3.

Inventory. List the data types you actually use, every stored procedure and function, SQL Agent jobs, cross-database queries, and linked servers. The surprises live here, so find them on day one.
Schema first. Translate types using the Part 3 mapping table, and move identifiers to snake_case per Part 2.
Move the data. The workhorse is pgloader — point it at both servers and it migrates SQL Server schema and data in one command:
```
pgloader mssql://app:secret@sqlhost/ShopDb pgsql://app:secret@pghost/shopdb
```
For fine-grained control, the manual route is bcp out to CSV and COPY in — tedious but transparent.
Translate the code paths. Queries via the Part 4 cheat sheet; procedures and functions via Part 5.
Rebuild the ops. Backups per Part 7, and SQL Agent jobs move to cron, systemd timers, or the pg_cron extension.
Run both in parallel. Dual-write or replicate for a while, compare row counts and reports, and let the new system earn trust.
Cut over with a rollback plan. A dated, verified backup of both systems, a maintenance window, and a written "how we go back" page you hopefully never open.

What has no direct equivalent

Candor time. Some parts of the SQL Server box simply are not in the PostgreSQL box:

SQL Server components without a built-in PostgreSQL counterpart
SQL Server	The PostgreSQL-world answer
SSRS	External reporting tools — Grafana, Metabase, JasperReports
SSIS	ETL alternatives — pgloader, Airflow, or plain .NET services
Linked servers	`postgres_fdw` and other foreign data wrappers
SQL Server Agent	pg_cron extension, OS cron, systemd timers

Gotcha: budget real time for anything in that table during your inventory step. "We only have two SSIS packages" has a way of becoming the longest line item in the whole migration.

That's a wrap 🎉

Look at what you have covered in eight parts:

Part 1 — why PostgreSQL is worth a SQL Server developer's attention.
Part 2 — clusters, databases, schemas, and roles, mapped to what you know.
Part 3 — the data type dictionary, from NVARCHAR to text.
Part 4 — the T-SQL to PostgreSQL phrasebook for everyday queries.
Part 5 — procedures, functions, and PL/pgSQL.
Part 6 — indexes, MVCC, VACUUM, and performance thinking.
Part 7 — backup, restore, and PITR without fear.

Here is the thing I hope this series proved: your years with SQL Server were not baggage to unlearn — they were the head start. Every concept transferred; only the vocabulary changed. You did not learn a database from scratch. You learned a dialect. That is why this was fast, not hard.

Where to next? If you are building the app side too, the Blazor series starts from the first file, and Docker explained is a friendly way to run PostgreSQL 18 locally without installing a thing. And if there is a topic you want covered — a migration war story, a PostgreSQL feature you keep hearing about — tell me through the contact page. I read every message.

Thanks for making the whole journey. Now go migrate something. 🐘