With the Npgsql EF Core provider 10, how do you map a C# enum to a native PostgreSQL enum type, and what does MapEnum set up?
answer
- enums are integer columns by default
- MapEnum inside UseNpgsql's options
- snake_case type and labels
- CREATE TYPE ... AS ENUM
- removing a label throws
basics
~20 sCall MapEnum<T>() inside UseNpgsql's options callback. The Npgsql provider then maps the enum on the data source it builds and adds the PostgreSQL enum to the model, so migrations emit CREATE TYPE and the column uses that type.
solid answer
~40 sBy default EF Core stores a C# enum as its underlying integer, and `HasConversion<string>()` would store member names as text. With the Npgsql provider 10, `UseNpgsql(cs, o => o.MapEnum<AppointmentStatus>("appointment_status"))` does two things: it maps the enum on the `NpgsqlDataSource` the provider builds for that connection string, and a model convention calls `HasPostgresEnum`, so the next migration emits `CREATE TYPE appointment_status AS ENUM (...)` and the `Status` column uses that type. Names go through `NpgsqlSnakeCaseNameTranslator` by default, so `CheckedIn` becomes `checked_in`, and `[PgName]` overrides a label. Adding a member later generates `ALTER TYPE ... ADD VALUE`; removing one makes migration SQL generation throw `NotSupportedException`. If you hand `UseNpgsql` your own `NpgsqlDataSource`, EF uses it as built, so map the enum on its `NpgsqlDataSourceBuilder` too.
code
csharp · 11 linespublic enum AppointmentStatus { Scheduled, CheckedIn, Completed, NoShow }
builder.Services.AddDbContext<ClinicContext>(options =>
options.UseNpgsql(
builder.Configuration.GetConnectionString("Clinic"),
npgsql => npgsql.MapEnum<AppointmentStatus>("appointment_status")));
// queries compare against the native type
var noShows = await db.Appointments
.Where(a => a.Status == AppointmentStatus.NoShow)
.CountAsync();go deeper
Recall that EF Core stores enums as integers by default and that MapEnum in UseNpgsql's options maps them to a native PostgreSQL enum.
Explain the two things MapEnum registers, the snake_case name translation and the migration SQL it produces.
Show how you evolve the enum safely, handle removed or renamed labels with raw SQL, and wire an externally built NpgsqlDataSource.
Weigh native enums against integer or text columns for data that outlives one application, including reporting tools and other database clients.
## The default: enums as integers EF Core maps a C# `enum` property to its **underlying integer** on every relational provider. The clinic app on SQL Server therefore stores `AppointmentStatus` in an `int` column, and moving to PostgreSQL keeps an `integer` column unless you choose otherwise. PostgreSQL also has **enum types**, created with `CREATE TYPE ... AS ENUM`, which store a small label set, reject values outside it and read naturally in SQL. ## Three ways to store the status | Mapping | Configuration | Column | Trade-off | |---|---|---|---| | Integer (default) | nothing | `integer` | compact, but `2` means nothing to someone reading the table | | Text | `HasConversion<string>()` | `text` | readable; any string is accepted unless a check constraint is added | | Native enum | `MapEnum<AppointmentStatus>()` | `appointment_status` | readable, constrained by the type, needs migrations to evolve | ## What MapEnum sets up `MapEnum<T>(string? enumName = null, string? schemaName = null, INpgsqlNameTranslator? nameTranslator = null)` lives on `NpgsqlDbContextOptionsBuilder`, the callback you pass to `UseNpgsql`. Calling it: 1. registers the enum on the **`NpgsqlDataSource`** the provider builds for that connection string, so the ADO.NET driver can read and write the type; 2. lets a model convention call **`HasPostgresEnum`**, so migrations know the type exists and create it; 3. maps every property of that CLR enum type to the PostgreSQL type, so the column is declared as `appointment_status`; 4. translates names with **`NpgsqlSnakeCaseNameTranslator`** unless you pass another translator: `AppointmentStatus` becomes `appointment_status`, `CheckedIn` becomes `checked_in`, and a `[PgName("...")]` attribute on a member overrides its label. ## The migration it produces The first migration after adding `MapEnum` emits the type before the table that uses it. The provider writes the labels in ordinal order of their names, so the type lists `checked_in, completed, no_show, scheduled`. Because PostgreSQL sorts enum values by their declared position, `OrderBy(a => a.Status)` then follows that label order, not the C# numeric values. Passing `schemaName` creates the type in another schema; the generator ensures that schema exists before the `CREATE TYPE` statement, since the model differ does not track enums when it creates schemas. Passing a different `INpgsqlNameTranslator`, such as `NpgsqlNullNameTranslator`, keeps the C# names unchanged. ## Evolving the enum - **Adding a member** generates `ALTER TYPE appointment_status ADD VALUE 'cancelled'`, positioned with `BEFORE` or `AFTER` where needed. - **Removing a member** cannot be expressed: generating the migration's SQL throws `NotSupportedException` ("Can't remove enum label ..."), pointing you to a raw SQL migration. - **Renaming a member** is likewise left to a raw SQL migration; the provider does not detect renames. ## Choosing between the three - Pick the **integer** default when the table is only ever read through the application and compact storage matters. - Pick **text** when other tools read the table and the value set changes often, adding a check constraint if invalid values must be rejected. - Pick a **native enum** when the value set is stable, other database clients should see readable values, and the team is comfortable evolving the type through migrations. ## Moving the clinic's existing data When the clinic's data arrives from SQL Server as integers, the switch to a native enum is a data migration, not just a model change: 1. Create the type, as the generated migration does. 2. Add a new column of the enum type. 3. Fill it with an `UPDATE` that maps each integer to its label, using `CASE` in a raw SQL migration. 4. Drop the integer column and rename the new one. ## Pitfalls when wiring it up - If you build an `NpgsqlDataSource` yourself and pass it to `UseNpgsql(dataSource)`, EF uses it **as built** and cannot add the mapping; call `MapEnum<AppointmentStatus>()` on its `NpgsqlDataSourceBuilder` as well. - If you pass an open `DbConnection` instead, the provider does not create a data source at all, so the driver-side mapping is yours to set up. - Moving existing data from the SQL Server integer column needs a conversion step, because PostgreSQL will not cast an integer to the enum type implicitly.
- The team adds Cancelled to AppointmentStatus; what does the next migration do on PostgreSQL?The model now lists an extra label, so the Npgsql migrations SQL generator emits `ALTER TYPE appointment_status ADD VALUE 'cancelled'`, with `BEFORE` or `AFTER` to place it among the existing labels. Existing rows are untouched. Until every running version of the app knows `Cancelled`, avoid writing it, because older code cannot map the new label to its enum when it reads such a row.
- Why does mapping the enum fail at runtime when the app registers its own NpgsqlDataSource?When `UseNpgsql` receives a ready-made data source, the provider uses it unchanged and cannot apply `MapEnum` to it, so the driver does not know how to read or write the PostgreSQL type. Map the enum on the `NpgsqlDataSourceBuilder` that builds that data source as well as on the EF options.
- Without MapEnum, EF Core stores C# enums as text in PostgreSQL.
- Removing an enum member is handled automatically by the next migration.
- MapEnum on the EF options also configures an NpgsqlDataSource you built yourself.
- PostgreSQL labels keep the C# member names, such as CheckedIn, by default.
- MapEnum alone changes nothing in the database until you write CREATE TYPE by hand.