Implementing Entity Framework Core with PostgreSQL

PostgreSQL is a powerful open‑source relational database. Paired with Entity Framework Core (EF Core), it enables developers to build robust, data‑driven .NET applications. EF Core is an object‑relational mapper (ORM) that lets you work with PostgreSQL through strongly typed C# models and LINQ instead of raw SQL.

Prerequisites

Before diving into implementation, ensure you have the following:

• .NET 6 or later installed

• PostgreSQL server installed locally or accessible remotely

• A preferred IDE (Visual Studio, JetBrains Rider, or VS Code)

Create a New .NET Project

Open your terminal or command prompt and create a new console or web project:

dotnet new webapi -n EfPostgresDemo
cd EfPostgresDemo

Install Required NuGet Packages

EF Core requires specific provider packages to communicate with PostgreSQL. Run the following commands:

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

• Microsoft.EntityFrameworkCore → Core EF libraries

• Npgsql.EntityFrameworkCore.PostgreSQL → PostgreSQL EF provider

• Microsoft.EntityFrameworkCore.Tools → Tools for migrations

Configure the Database Context

Create a Data folder and add a class named AppDbContext.cs:

using Microsoft.EntityFrameworkCore;

namespace EfPostgresDemo.Data
{
    public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

        // Define DbSets (tables)
        public DbSet<Product> Products { get; set; }
    }

    // Example entity
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = "";
        public decimal Price { get; set; }
    }
}

Add Connection String

Open appsettings.json and configure your PostgreSQL connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=ef_demo;Username=postgres;Password=yourpassword"
  }
}

Register EF Core in Program.cs

Modify Program.cs to use AppDbContext:

using EfPostgresDemo.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Register EF Core with PostgreSQL
builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.AddControllers();
var app = builder.Build();

app.MapControllers();
app.Run();

Run Migrations

Now that EF Core is configured, create your first migration and update the database:

dotnet ef migrations add InitialCreate
dotnet ef database update

This will create a Products table inside the ef_demo PostgreSQL database.

Use EF Core in Your Application

Example: add a simple API controller to manage products.

Create Controllers/ProductController.cs:-

using EfPostgresDemo.Data;
using Microsoft.AspNetCore.Mvc;

namespace EfPostgresDemo.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class ProductController : ControllerBase
    {
        private readonly AppDbContext _context;
        public ProductController(AppDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public IActionResult GetProducts()
        {
            return Ok(_context.Products.ToList());
        }

        [HttpPost]
        public IActionResult AddProduct(Product product)
        {
            _context.Products.Add(product);
            _context.SaveChanges();
            return Ok(product);
        }
    }
}

Testing the Application

Run the application

dotnet run

Navigate to

• GET /api/product → Fetch all products

• POST /api/product → Add a new product (send JSON payload in Postman or Swagger)

Example payload

{
  "name": "name",
  "price": 1000.00
}

Conclusion

By combining Entity Framework Core and PostgreSQL, you gain the flexibility of a modern ORM with the robustness of a powerful database system. This setup is suitable for microservices, enterprise systems, and cloud-native applications.

With EF Core’s migrations, LINQ queries, and PostgreSQL’s advanced features like JSON support, you’re well-equipped to build scalable and efficient data-driven solutions.