A first-draft EF Core entity is easy to write and easy to regret six months later. Column type mismatches, missing indexes, and a DbContext clogged with hundreds of lines of Fluent API calls are all fixable problems, but they are far cheaper to avoid upfront. This post covers the model design patterns that hold up well when PostgreSQL is your target database.
By the end you will have a DbContext organized with IEntityTypeConfiguration classes, column types chosen deliberately for PostgreSQL semantics, and indexes defined in the right layer of the application.
Before starting, make sure you have:
- The project from the first post in this series, or any .NET 8 Web API with
Npgsql.EntityFrameworkCore.PostgreSQLinstalled - EF Core CLI tool:
dotnet tool install --global dotnet-ef - Basic familiarity with EF Core Fluent API conventions
Npgsql.EntityFrameworkCore.PostgreSQL 8.x. The LegacyTimestampBehavior AppContext switch is assumed to be off, which is the default for new projects.
Understanding the convention defaults lets you apply Fluent API only where you need to override them, which keeps configuration classes focused.
// A convention-friendly pair of entities
public class Order
{
public int Id { get; set; } // PK inferred from "Id"
public string CustomerEmail { get; set; } = string.Empty;
public decimal Total { get; set; }
public OrderStatus Status { get; set; }
public DateTimeOffset PlacedAt { get; set; }
public List<OrderLine> Lines { get; set; } = [];
}
public class OrderLine
{
public int Id { get; set; }
public int OrderId { get; set; } // FK inferred from nav + "TypeNameId" pattern
public Order Order { get; set; } = null!;
public string Sku { get; set; } = string.Empty;
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
}
public enum OrderStatus { Pending, Confirmed, Shipped, Cancelled }EF Core infers the primary key from a property named Id or <TypeName>Id. It infers a foreign key when a navigation property and a matching <TypeName>Id shadow property exist on the dependent entity. String properties without HasMaxLength become text columns in PostgreSQL, which is valid but worth making explicit in any column that benefits from a database-enforced length constraint.
Rather than putting all Fluent API calls in OnModelCreating, implement IEntityTypeConfiguration<T> in a dedicated class per entity. Each class lives in a Data/Configurations folder and is discovered automatically through assembly scanning.
// Data/Configurations/OrderConfiguration.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
builder.ToTable("orders");
builder.Property(o => o.CustomerEmail)
.IsRequired()
.HasMaxLength(256);
builder.Property(o => o.Total)
.HasColumnType("numeric(18,4)");
builder.Property(o => o.Status)
.HasConversion<string>()
.HasMaxLength(32);
builder.Property(o => o.PlacedAt)
.HasColumnType("timestamp with time zone");
builder.HasIndex(o => o.CustomerEmail)
.HasDatabaseName("ix_orders_customer_email");
}
}Storing the enum as a string with HasConversion<string>() makes the column readable without a lookup table and keeps migrations straightforward. If you prefer PostgreSQL-native enum types, Npgsql supports them via MapEnum, but that approach carries extra migration discipline that the next post covers in detail.
OrderLine configuration
// Data/Configurations/OrderLineConfiguration.cs
public class OrderLineConfiguration : IEntityTypeConfiguration<OrderLine>
{
public void Configure(EntityTypeBuilder<OrderLine> builder)
{
builder.ToTable("order_lines");
builder.Property(l => l.Sku)
.IsRequired()
.HasMaxLength(64);
builder.Property(l => l.UnitPrice)
.HasColumnType("numeric(18,4)");
builder.HasIndex(l => new { l.OrderId, l.Sku })
.HasDatabaseName("ix_order_lines_order_sku");
builder.HasOne(l => l.Order)
.WithMany(o => o.Lines)
.HasForeignKey(l => l.OrderId)
.OnDelete(DeleteBehavior.Cascade);
}
}Call ApplyConfigurationsFromAssembly in OnModelCreating to pick up every IEntityTypeConfiguration<T> class in the assembly automatically.
// Data/CatalogDbContext.cs
using Microsoft.EntityFrameworkCore;
public class CatalogDbContext : DbContext
{
public CatalogDbContext(DbContextOptions<CatalogDbContext> options)
: base(options) { }
public DbSet<Order> Orders => Set<Order>();
public DbSet<OrderLine> OrderLines => Set<OrderLine>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfigurationsFromAssembly(typeof(CatalogDbContext).Assembly);
}
}Adding a new entity now requires only creating a new configuration class. The DbContext itself stays unchanged. This is the single biggest quality-of-life improvement you can make in a DbContext that will grow over time.
Npgsql provides sensible defaults, but a few mappings need an explicit call to behave correctly in production.
Decimal precision
EF Core maps decimal to numeric but does not set precision and scale unless you tell it to. Without an explicit HasColumnType("numeric(p,s)"), the generated column uses the database's default precision, which can cause silent rounding. Always set precision and scale for money or measurement columns.
DateTime vs DateTimeOffset
With LegacyTimestampBehavior off (the default in new .NET 8 projects), Npgsql maps DateTime only if it is UTC-kind and emits a compile-time warning for anything else. Use DateTimeOffset for timestamps that cross time zones and map them to timestamp with time zone explicitly in your configuration.
text vs character varying
In PostgreSQL, text and varchar have identical storage and performance. Use text for open-ended string columns and call HasMaxLength only where you want the database to enforce a maximum length.
jsonb columns
// Mapping a metadata bag to jsonb
builder.Property(o => o.Metadata)
.HasColumnType("jsonb")
.HasDefaultValueSql("'{}'");Npgsql can serialize and deserialize .NET types directly to and from jsonb. Indexing a jsonb column with a GIN index is possible through raw SQL in a migration, which the next post demonstrates.
Generate the migration and inspect the SQL before applying it to any shared environment.
dotnet ef migrations add AddOrdersSchema
dotnet ef migrations script --idempotentIn the generated script, check that every decimal column shows the correct numeric(p,s), that all timestamp columns are timestamp with time zone, and that your named indexes appear with the names you defined. Once satisfied, apply with dotnet ef database update.
orders, order_lines). PostgreSQL folds unquoted identifiers to lowercase, so using snake_case in your Fluent API avoids the quoting gymnastics that arise when EF Core generates PascalCase table names by default.
You now have a DbContext built around per-entity IEntityTypeConfiguration classes, column types locked in deliberately for PostgreSQL, and a consistent naming strategy that avoids identifier quoting issues at runtime.
The next post goes deeper into migrations: how EF Core and Npgsql generate them, the pitfalls specific to PostgreSQL (enum types, schema-qualified names, case sensitivity), and how to apply migrations safely in a CI/CD pipeline without blocking deployments or corrupting the migration history.
No comments: