`=Introduction= So this page is to nudge my little gray cells need time I think of the Entity Framework (EF) and PostgreSQL

Packages

As of Jan 2026 I used this

    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.1" />
    <PackageReference Include="Npgsql" Version="10.0.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.0" />

Areas for Implementation

So we need to think about the following parts of the code when implementing the EF with PostgreSQL

Context
Connection Configuration
Table Configuration
Repository

Context

For the context, this is where you specify what entities you are going to be handling. The ApplyConfigurationsFromAssembly performs the registering of the generated table configurations

namespace Infrastructure.Persistence.EF.Context;

using Domain.Entities;

using Microsoft.EntityFrameworkCore;

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<EntityActor> Actors => Set<EntityActor>();
    public DbSet<EntityFilm> Films => Set<EntityFilm>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}

Connection Configuration

We need to configure our connection. We do this at startup.

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseNpgsql(
                    dbConfig.ConnectionString,
                    // Discussed below npgsqlOptions => npgsqlOptions.MapEnum<DbRating>("mpaa_rating")
                );
            });

Table Configuration

This is provide info on your table to allow EF to work. Not shown here but I think you can say relationship and other things too. This is a simplish example to demonstrate. This will map the columns to the Entity. I guess HasKey is obvious along with HasDefaultValueSql

public class ActorConfiguration : IEntityTypeConfiguration<EntityActor>
{
    public void Configure(EntityTypeBuilder<EntityActor> builder)
    {
        builder.ToTable("actor");

        builder.HasKey(a => a.ActorId);

        builder.Property(a => a.ActorId)
            .HasColumnName("actor_id");

        builder.Property(a => a.FirstName)
            .HasColumnName("first_name");

        builder.Property(a => a.LastName)
            .HasColumnName("last_name");

        builder.Property(f => f.LastUpdate)
            .HasColumnName("last_update")
            .HasColumnType("timestamp")
            .HasDefaultValueSql("now()")
            .IsRequired();

    }
}

Repository

This is where you actually access the database. Not of it is complicated once set up above is completed.

// Get 

                    return await _db.Actors
                         .AsNoTracking()
                        .FirstOrDefaultAsync(a => a.ActorId == actorId, ct);
// Add
                    _db.Actors.Add(actor);
                    await _db.SaveChangesAsync(token);
// Delete
                    _db.Actors.Remove(actor);
                    await _db.SaveChangesAsync(token);
// Update
                    var existing = await _db.Actors.FirstOrDefaultAsync(a => a.ActorId == actor.ActorId, token);
                    if (existing is null)
                        return null;

                    _db.Entry(existing).CurrentValues.SetValues(actor);
                    await _db.SaveChangesAsync(token);

PostgreSQL Custom Types Walk Through

My Custom Type

So in DVD Rental there is a custom type of the table Film. Shown below

dvdrental-> \dT+ mpaa_rating
                                         List of data types
 Schema |    Name     | Internal name | Size | Elements |  Owner   | Access privileges | Description
--------+-------------+---------------+------+----------+----------+-------------------+-------------
 public | mpaa_rating | mpaa_rating   | 4    | G       +| postgres |                   |
        |             |               |      | PG      +|          |                   |
        |             |               |      | PG-13   +|          |                   |
        |             |               |      | R       +|          |                   |
        |             |               |      | NC-17    |          |                   |

Wanted to walk through how to do this in EF+PostgreSQL. You can see that the possibilities suggest an enum but custom types do support composites

CREATE TYPE full_name AS (
    first_name  text,
    last_name   text,
    name_length int
);

I asked how this would be done via the robot so I could see how I would approach something a tad simpler. So onward with Rating.

What's wrong with Enum

Glad we are not on Typescript. What's wrong with Enum is you cannot have a hyphen in one. So my Domain Enum for this is like this with no hyphen. So we need to be able to read the original e.g. PG-13 and translate to PG13

namespace Domain.ValueObjects;

public enum Rating
{
    G,
    PG,
    PG13,
    R,
    NC17
}

Register the type

So as mentions above we need to register the type so EF knows it is a type. hence

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseNpgsql(
                    dbConfig.ConnectionString,
                    npgsqlOptions => npgsqlOptions.MapEnum<DbRating>("mpaa_rating")
                );
            });

Create a Infra Enum

We can map the DB enums to the Rating values. Note the using of NpgsqlTypes, we would not want this in Domain.

namespace Infrastructure.Persistence.EF.ValueObjects;

using NpgsqlTypes;

public enum DbRating
{
    [PgName("G")] G,
    [PgName("PG")] PG,
    [PgName("PG-13")] PG13,
    [PgName("R")] R,
    [PgName("NC-17")] NC17
}

Configure Column in Film

Now we can configure the column. With this we can specify the translation from DbRating to Rating.

public sealed class FilmConfiguration : IEntityTypeConfiguration<EntityFilm>
{
    public void Configure(EntityTypeBuilder<EntityFilm> builder)
    {
        builder.ToTable("film");

        // Primary key
        builder.HasKey(f => f.FilmId);

        builder.Property(f => f.FilmId)
            .HasColumnName("film_id")
            .UseIdentityAlwaysColumn()
            .IsRequired();
...
        builder.Property(f => f.Rating)
            .HasColumnName("rating")
            .HasColumnType("mpaa_rating")
            .HasConversion(
                v => (DbRating)v,
                v => (Rating)v
            );
...
    }
}

Alternative Approach

Whilst writing this up the Robot was kind enough to suggest and better approach using EnumMember. I like the conversion demo but remove Npgsql stuff out sounds better. So we could have an infra DbRating with.

using System.Runtime.Serialization;

public enum DbRating { 
 [EnumMember(Value = "G")] G,
 [EnumMember(Value = "PG")] PG,
 [EnumMember(Value = "PG-13")] PG13,
 [EnumMember(Value = "R")] R,
 [EnumMember(Value = "NC-17")] NC17
}

You must tell Npgsql to use the EnumMember attributes:

 
services.AddDbContext<AppDbContext>(options =>
{
    options.UseNpgsql(
        dbConfig.ConnectionString,
        npgsqlOptions => npgsqlOptions.MapEnum<DbRating>("mpaa_rating")
    );
});

And for the column configuration no change

 
builder.Property(f => f.Rating)
    .HasColumnName("rating")
    .HasColumnType("mpaa_rating")
    .HasConversion(
        v => (DbRating)v,   // Domain → Infra → DB
        v => (Rating)v      // DB → Infra → Domain
    );

NpgsqlDataSource

General

Looking at this when converting from Native to EF with just DbContext, we get

Parses the connection string
Creates connections ad‑hoc
Doesn’t know about Npgsql plugins
Can’t use multiplexing
Can’t share a unified connection pipeline with Dapper or raw Npgsql
Has no access to custom type handlers or global JSON settings

Switching

Old DbContext

We having used the DbContext we could not configure programmatically the MaxPoolSize,MinPoolSize, ConnectionIdleLifetime so this is a great reason to switch. Currently we could have

services.AddDbContext<AppDbContext>(options =>
{
    options.UseNpgsql(
        dbConfig.ConnectionString,
        npgsqlOptions =>
        {
            npgsqlOptions.MapEnum<DbRating>("mpaa_rating");
            npgsqlOptions.CommandTimeout(dbConfig.CommandTimeoutSeconds);
        });
});

With this added to the connection string.

... MinPoolSize=5;MaxPoolSize=10;ConnectionIdleLifetime=5;

Not that must of a drawback but enough.

New Approach

So here is a revised approach and should be noted I forgot to put TLS in for the old approach. This got quite length so when for the an extension. First the IServiceCollection part which looks very obvious on what it is doing.

            var builder = new NpgsqlDataSourceBuilder(dbConfig.ConnectionString)
                .ConfigurePooling(dbConfig)
                .ConfigureTls(dbConfig)
                .ConfigureTimeouts(dbConfig)
                .ConfigureEnums();

            var dataSource = builder.Build();

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseNpgsql(dataSource);
            });

Now the extension

namespace Infrastructure.Extensions;

using Infrastructure.Persistence.Common.Configuration;
using Infrastructure.Persistence.EF.ValueObjects;

using Npgsql;

public static class NpgsqlBuilderExtensions
{
    public static NpgsqlDataSourceBuilder ConfigurePooling(
        this NpgsqlDataSourceBuilder builder,
        DatabaseConfig config)
    {
        builder.ConnectionStringBuilder.MinPoolSize = config.MinPoolSize;
        builder.ConnectionStringBuilder.MaxPoolSize = config.MaxPoolSize;

        // Npgsql 10 rule: IdleLifetime >= PruningInterval (default 10)
        builder.ConnectionStringBuilder.ConnectionIdleLifetime =
            Math.Max(config.ConnectionIdleLifetime, 10);

        return builder;
    }

    public static NpgsqlDataSourceBuilder ConfigureTls(
        this NpgsqlDataSourceBuilder builder,
        DatabaseConfig config)
    {
        builder.ConnectionStringBuilder.SslMode =
            string.IsNullOrEmpty(config.RootCertificatePath)
                ? SslMode.Require
                : SslMode.VerifyFull;

        if (!string.IsNullOrEmpty(config.RootCertificatePath))
        {
            builder.ConnectionStringBuilder.RootCertificate = config.RootCertificatePath;
        }

        return builder;
    }

    public static NpgsqlDataSourceBuilder ConfigureTimeouts(
        this NpgsqlDataSourceBuilder builder,
        DatabaseConfig config)
    {
        builder.ConnectionStringBuilder.CommandTimeout = config.CommandTimeoutSeconds;
        return builder;
    }

    public static NpgsqlDataSourceBuilder ConfigureEnums(
        this NpgsqlDataSourceBuilder builder)
    {
        builder.MapEnum<DbRating>("mpaa_rating");
        return builder;
    }
}

Tracking vs NoTracking

So this seems to be some kind of memory management for Entities inside of Entity Framework. The suggestion is, for instance, without tracking on a the Id would not be returned to the Entity.

                    _db.Actors.Add(actor);
                    await _db.SaveChangesAsync(token);

Rule of thumb, GET is no tracking, POST, PUT, PATCH, DEL tracking

Logging & diagnostics

Pretty easy to set up, just add stuff to the AddDbContext

            services.AddDbContext<AppDbContext>(options =>
            {
                options.UseNpgsql(dataSource);
                options.EnableSensitiveDataLogging();
                options.EnableDetailedErrors(false); // Probably best on my wiki
                options.LogTo(message => logger.LogInformation("{Message}", message), LogLevel.Information);
            });

This gave me this on the console.

info: InfrastructureStartup[0]
      warn: 01/10/2026 16:37:55.426 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
            Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: InfrastructureStartup[0]
      info: 01/10/2026 16:37:55.882 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
            Executed DbCommand (34ms) [Parameters=[@actorId='15'], CommandType='Text', CommandTimeout='30']
            SELECT a.actor_id, a.first_name, a.last_name, a.last_update
            FROM actor AS a
            WHERE a.actor_id = @actorId
            LIMIT 1
info: Infrastructure.Persistence.EF.Repositories.RepositoryActorEf[0]
      DB operation ActorGetById succeeded in 723.9534ms

But we can do better given my telemetry. Plug into OpenTel and we get the connection from Npgsql and the SQL from EF instrumentation

        // Configure basic tracing without external exporters first
        tracerProviderBuilder
            .AddSamplerWhen(telemetryConfig.Environment == "Development")

            // Add ASP.NET Core instrumentation
            .AddAspNetCoreInstrumentation()

            // Add EF Core instrumentation
            .AddEntityFrameworkCoreInstrumentation(options =>
            {
                options.EnrichWithIDbCommand = (activity, command) =>
                {
                    var stateDisplayName = $"{command.CommandType} main";
                    activity.DisplayName = stateDisplayName;
                    activity.SetTag("db.name", stateDisplayName);
                };
            })

            // Add Npgsql instrumentation
            .AddNpgsql()

            // .AddHttpClientInstrumentation()
            .AddSource(telemetryConfig.ServiceName)
            .AddRedisInstrumentation()
            .AddConsoleExporterWhen(telemetryConfig.EnableConsoleExporter == true && telemetryConfig.Environment == "Development")
            .AddOtlpExporterWhen(honeycombConfig, telemetryConfig.Enabled);

        return tracerProviderBuilder;

LINQ Translation Notes

EF Core translates LINQ expression trees into SQL. Only expressions that can be converted into SQL will run on the database server. Everything else will throw
An example of an expression is

var actors = await _db.Actors
    .Where(a => a.LastName == "Smith")   // runs on SQL
    .Select(a => new 
    {
        a.ActorId,
        FullName = $"{a.FirstName} {a.LastName}" // runs in .NET after SQL
    })
    .ToListAsync(ct);

a translation exception.

What EF Core Can Translate

Basic comparisons (==, !=, >, <, >=, <=)
Boolean logic (&&, ||, !)
String operations (StartsWith, Contains, EndsWith, ToLower, ToUpper)
Math operations (+, -, *, /)
Date/time operations (AddDays, Date, Year, Month, etc.)
Navigation properties and joins
Aggregates (Any, All, Count, Sum, Min, Max)
Most IQueryable operators (Where, Select, OrderBy, GroupBy*)

What EF Core Cannot Translate

Arbitrary .NET methods
Local functions or lambdas not expressible as expression trees
Regex or complex string parsing
Reflection
Domain logic inside predicates
Value objects without EF mapping or converters
Large client-side collections inside Contains()

Translation Boundary

EF only translates expression trees. Any code executed before the expression tree is built runs on the client. Any code inside the expression tree must be translatable to SQL.

Rule of Thumb

If PostgreSQL can do it, EF can probably translate it. If PostgreSQL cannot do it, EF definitely cannot translate it.

Naming Conventions

C# Domain Model

Use PascalCase for:

Entity classes
Value objects
Properties
Navigation properties

Example: public string FirstName { get; set; }

EF Core Model

Enable snake_case mapping: modelBuilder.UseSnakeCaseNamingConvention();

This ensures:

ActorId → actor_id
FirstName → first_name
LastUpdate → last_update

PostgreSQL Schema

Use snake_case for:

Tables
Columns
Foreign keys
Indexes
Constraints

Example: CREATE TABLE actor (

   actor_id int PRIMARY KEY,
   first_name text NOT NULL

);

Why This Matters

Consistent naming ensures:

Correct LINQ translation
Predictable SQL generation
Fewer mapping bugs
Cleaner diagnostics
Easier debugging

Robots Repository Pattern Caveats

The Repository Pattern is a well‑known abstraction for data access, but when used with EF Core it introduces several practical caveats. EF Core already provides many of the capabilities that repositories traditionally offer, and layering another abstraction on top can hide important behaviour, reduce composability, and create subtle bugs.

1. Loss of Query Composability

EF Core queries are composable. A repository method that returns a fully materialized list removes the ability to: - Apply dynamic filters - Apply dynamic sorting - Apply pagination - Combine queries - Reuse query fragments Returning IEnumerable<T> instead of IQueryable<T> forces all downstream filtering to run in memory.

2. Hidden LINQ Translation Errors

EF Core throws translation exceptions when a LINQ expression cannot be converted to SQL. If a repository materializes early (e.g., ToListAsync() inside the repository), translation errors are hidden until runtime or never surface at all.

3. Accidental Client‑Side Evaluation

If a repository returns IEnumerable<T>, any additional filtering runs client‑side. This can cause: - Full table scans - Poor performance - Missing index usage - Silent correctness issues EF Core cannot optimize queries it never sees.

4. Duplication of EF Core’s Unit of Work

EF Core’s DbContext is already a Unit of Work. Repositories that wrap SaveChanges or manage transactions duplicate this responsibility and can interfere with: - Change tracking - Concurrency tokens - Batching - Retry policies - Transaction scopes

5. Loss of Change Tracking Semantics

Repositories often return entities with AsNoTracking() or reattach detached entities. This breaks: - Partial updates - Owned types - Navigation fix‑up - Concurrency resolution Tracking is contextual; repositories hide that context.

6. Hidden Transactions and Concurrency Behaviour

Repositories that wrap operations in try/catch or call SaveChanges internally obscure: - Transaction boundaries - Retry logic - Concurrency exceptions - Execution strategies This makes debugging harder and behaviour less predictable.

7. Anemic Repository Interfaces

Most repositories devolve into: - Add - Update - Delete - GetById - GetAll These methods are not expressive, not domain‑driven, and not intention‑revealing. They often force consumers to re‑implement filtering and business logic outside the repository.

8. Reduced Observability

EF Core emits rich diagnostics: - SQL spans - Npgsql spans - Connection pool events - Translation warnings Repositories that hide the DbContext or materialize early reduce visibility into what EF is doing.

9. Harder, Not Easier, Testing

The original justification for repositories was testability. In practice: - EF Core is rarely swapped out - Repository mocks do not behave like EF - Tests become inaccurate - Integration tests become more important Modern testing uses EF Core InMemory or Testcontainers, not repository mocks.

10. Two Sources of Truth

EF Core already knows: - Table names - Column names - Keys - Relationships - Constraints Repositories often duplicate this knowledge in method names, parameters, or custom SQL, creating drift.

CSharp Entity Framework PostgreSQL