EF Core and PostgreSQL: Getting Started Guide

To use PostgreSQL with EF Core, install the Npgsql.EntityFrameworkCore.PostgreSQL package and call UseNpgsql with your connection string when you register the DbContext. Migrations, LINQ queries, and change tracking work the same as with SQL Server. Npgsql also exposes JSONB, arrays, and full-text search, while timestamp and naming conventions require deliberate choices.

PostgreSQL is not SQL Server with a different connection string. A clean provider setup gives you those PostgreSQL capabilities without leaking database-specific details through the whole application.

Why PostgreSQL With EF Core?

PostgreSQL offers features that SQL Server doesn't - JSONB columns, native array types, full-text search, and range types. It's also free and runs everywhere. The Npgsql provider for EF Core gives you access to all of these features.

Npgsql is the official PostgreSQL provider for EF Core, distributed as the Npgsql.EntityFrameworkCore.PostgreSQL NuGet package. It is mature, actively maintained, and exposes PostgreSQL-specific functionality through EF Core's configuration and query APIs.

Setting Up the Npgsql Provider

If you don't have PostgreSQL running yet, Docker is the quickest way:

docker run -d --name postgres \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:17

Install the NuGet package:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Configure your DbContext:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("Database")));

Connection string in appsettings.json:

{
  "ConnectionStrings": {
    "Database": "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=postgres"
  }
}

That's all you need to get started. EF Core migrations and queries work the same way as with SQL Server.

Snake_case Naming Convention

PostgreSQL conventions use snake_case for table and column names. By default, EF Core generates PascalCase names, which work but look out of place.

Use the naming conventions package:

dotnet add package EFCore.NamingConventions

Configure it:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString)
           .UseSnakeCaseNamingConvention());

Now an entity like this:

public class Order
{
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public OrderStatus Status { get; set; }
    public decimal TotalAmount { get; set; }
}

Generates a table with snake_case columns:

CREATE TABLE orders (
    id uuid NOT NULL,
    created_at timestamp with time zone NOT NULL,
    status integer NOT NULL,
    total_amount numeric NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (id)
);

JSONB Columns

PostgreSQL's jsonb type stores JSON data in a binary format that supports indexing and querying. EF Core maps this natively with owned types (the Npgsql provider supports ToJson since version 8):

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public ProductMetadata Metadata { get; set; }
}

public class ProductMetadata
{
    public string Brand { get; set; }
    public int? ReleaseYear { get; set; }
    public List<string> Tags { get; set; }
}

Stick to regular properties, primitive collections, and nested owned types inside the JSON type. Dictionary properties are not supported in ToJson mappings.

Configure as JSON:

public void Configure(EntityTypeBuilder<Product> builder)
{
    builder.OwnsOne(p => p.Metadata, meta =>
    {
        meta.ToJson();
    });
}

You can query into JSONB columns:

var products = await context.Products
    .Where(p => p.Metadata.Brand == "Contoso")
    .ToListAsync();

EF Core translates this into a PostgreSQL JSON query. For more complex queries, you can use raw SQL with JSON operators.

Array Types

PostgreSQL natively supports array columns. Npgsql maps .NET arrays and lists directly:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string[] Tags { get; set; }
    public List<int> Ratings { get; set; }
}

No special configuration needed. EF Core generates:

CREATE TABLE products (
    id uuid NOT NULL,
    name text NOT NULL,
    tags text[] NOT NULL,
    ratings integer[] NOT NULL
);

Query array columns with LINQ:

// Products that contain a specific tag
var products = await context.Products
    .Where(p => p.Tags.Contains("electronics"))
    .ToListAsync();

// Products with any matching tag
var searchTags = new[] { "electronics", "sale" };
var matching = await context.Products
    .Where(p => p.Tags.Any(t => searchTags.Contains(t)))
    .ToListAsync();

Arrays are great for simple lists of values where you don't need a separate table.

Full-Text Search

PostgreSQL has powerful built-in full-text search. Npgsql exposes it through EF Core:

var results = await context.Products
    .Where(p => EF.Functions.ToTsVector("english", p.Name + " " + p.Description)
        .Matches(EF.Functions.ToTsQuery("english", "laptop & gaming")))
    .ToListAsync();

For better performance, add a generated tsvector column:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public NpgsqlTsVector SearchVector { get; set; }
}

Configure it:

builder.Property(p => p.SearchVector)
    .HasComputedColumnSql(
        @"to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))",
        stored: true);

builder.HasIndex(p => p.SearchVector)
    .HasMethod("GIN");

Now queries use the precomputed index:

var results = await context.Products
    .Where(p => p.SearchVector.Matches(
        EF.Functions.ToTsQuery("english", "laptop & gaming")))
    .OrderByDescending(p => p.SearchVector.Rank(
        EF.Functions.ToTsQuery("english", "laptop & gaming")))
    .ToListAsync();

With an appropriate GIN index, PostgreSQL full-text search scales better than an unindexed LIKE or ILIKE scan on large datasets.

PostgreSQL-Specific Features

A few more features worth knowing:

UUID Primary Keys

PostgreSQL has native uuid support. Use Guid properties and they map to uuid columns:

builder.Property(o => o.Id)
    .HasDefaultValueSql("gen_random_uuid()");

Enum Mapping

Map .NET enums to PostgreSQL enum types:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString, npgsqlOptions =>
        npgsqlOptions.MapEnum<OrderStatus>("order_status")));

The UTC Timestamp Rule

This is the one that surprises everyone migrating from SQL Server. PostgreSQL's timestamptz stores instants in UTC, and since Npgsql 6, writing a DateTime with Kind = Unspecified or Local to a timestamptz column throws an exception.

Use DateTime.UtcNow for generated instants, or accept DateTimeOffset at the boundary and normalize it to offset zero before writing. Npgsql maps DateTimeOffset to timestamptz, but rejects non-zero offsets because PostgreSQL stores the UTC instant rather than the original offset.

If you're porting a legacy codebase and can't fix every timestamp at once, there's an escape hatch that restores the old behavior:

// Opt back into pre-Npgsql-6 behavior (not recommended for new code)
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Treat that switch as a migration aid, not a solution.

Stored Procedures and Functions

PostgreSQL functions work well with EF Core too - I cover calling them (and when to use them) in stored procedures and functions with EF Core and PostgreSQL.

Vector Search

If you're building AI features, the pgvector extension turns PostgreSQL into a capable vector database. See getting started with pgvector in .NET.

Summary

Start with Npgsql and make naming and UTC conventions explicit at the model boundary. Then use PostgreSQL features such as JSONB, arrays, full-text search, and native enums where they simplify the data model. Provider-specific capabilities are an advantage when the choice is deliberate and isolated from unrelated application code.