When moving an EF Core 10 clinic app from SQL Server to the Npgsql provider, which mappings and query behaviours change under the same model?
answer
- datetime2 becomes timestamptz
- only UTC DateTime values
- identity by default, serial below 10
- case-sensitive text comparisons
- EF.Functions differ per provider
basics
~20 sThe model stays, but the provider decides the rest: DateTime maps to timestamptz and accepts only UTC values, keys become identity-by-default columns, strings become text and compare case-sensitively, provider EF.Functions change, and SQL Server migrations must be regenerated.
solid answer
~40 sSwapping `UseSqlServer` for `UseNpgsql` keeps entity classes and LINQ but changes everything the provider decides. `DateTime` maps to `timestamp with time zone`, and Npgsql writes only `DateTimeKind.Utc` values there; a Local or Unspecified value throws instead of being converted. Integer keys become `GENERATED BY DEFAULT AS IDENTITY` columns, or serial below PostgreSQL 10, with `UseIdentityAlwaysColumns`, `UseSerialColumns` or `UseHiLo` as alternatives. Strings become `text`, and comparisons are case-sensitive under PostgreSQL's default collations, unlike SQL Server's usual case-insensitive ones, so a surname search needs `EF.Functions.ILike` or a collation. SQL Server-only functions such as `EF.Functions.DateDiffDay` or `FreeText` live in the SQL Server package and must be rewritten. Migrations carry provider annotations and store types, so generate a fresh set, and call `SetPostgresVersion` if the server differs from the assumed version 14.
code
csharp · 13 linesbuilder.Services.AddDbContext<ClinicContext>(options =>
options.UseNpgsql(
builder.Configuration.GetConnectionString("Clinic"),
npgsql => npgsql.SetPostgresVersion(16, 0)));
// a case-insensitive surname search, as SQL Server's collation gave for free
var patients = await db.Patients
.Where(p => EF.Functions.ILike(p.Surname, term + "%"))
.ToListAsync();
// timestamptz accepts only UTC DateTime values
appointment.StartsAt = TimeZoneInfo.ConvertTimeToUtc(localStart, clinicZone);
await db.SaveChangesAsync();go deeper
Recall that the provider decides store types and SQL, so DateTime, strings and keys map differently once UseNpgsql replaces UseSqlServer.
Explain the UTC-only rule for timestamptz, the identity-by-default convention and why string comparisons change behaviour.
Show how you would plan the switch: replacing store types and functions, regenerating migrations, fixing DateTime kinds and setting the server version.
Judge whether supporting two providers is worth it against committing to one, given migrations, tests and provider-specific features.
## What stays and what moves An EF Core model is mostly provider-neutral: entity classes, relationships, LINQ queries and `SaveChanges` work the same way. What changes is everything the **provider** decides: store types, key generation, how operators and functions translate, and the SQL in migrations. Moving the clinic app from `Microsoft.EntityFrameworkCore.SqlServer` to `Npgsql.EntityFrameworkCore.PostgreSQL` 10 is a provider swap, not a recompile. ## Default mappings side by side | CLR type | SQL Server provider | Npgsql provider | |---|---|---| | `int` key | `int` IDENTITY column | `integer GENERATED BY DEFAULT AS IDENTITY` | | `DateTime` | `datetime2` | `timestamp with time zone` | | `string` | `nvarchar(max)` | `text` | | `bool` | `bit` | `boolean` | | `Guid` | `uniqueidentifier` | `uuid` | | `List<string>` | a JSON array in a string column | a native `text[]` array | Explicit `HasColumnType("nvarchar(200)")` calls are SQL Server store types and must be replaced, for example with `HasMaxLength(200)`, which each provider turns into its own type. ## The DateTime trap Npgsql maps `DateTime` to `timestamp with time zone` and writes **only `DateTimeKind.Utc`** values to it. Writing a `Local` or `Unspecified` value throws: "Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported." SQL Server's `datetime2` stores whatever it is given, so code that worked for years can fail on the first save. The fixes: 1. Convert at the boundary, for example with `TimeZoneInfo.ConvertTimeToUtc(localStart, clinicZone)`, and store UTC. 2. For true wall-clock values, such as a clinic's opening hour, map the property to `timestamp without time zone`, which in turn rejects UTC-kind values. 3. The AppContext switch `Npgsql.EnableLegacyTimestampBehavior` restores the old mapping, as a temporary measure only. ## Case sensitivity and provider functions - SQL Server's default collations compare case-insensitively; PostgreSQL's default collations are case-sensitive. `Where(p => p.Surname == "okafor")` stops matching `Okafor`. - `EF.Functions.Like` follows the column's collation, so it becomes case-sensitive too. The Npgsql provider adds `EF.Functions.ILike`, and a collation configured with `UseCollation` can restore case-insensitive equality. - Functions are provider extension methods. `EF.Functions.DateDiffDay`, `FreeText` and `Contains` come from the SQL Server package and do not exist once it is removed; Npgsql offers its own, such as `ToTsVector` for full-text search. ## Keys, versions and migrations - The Npgsql convention picks `IdentityByDefaultColumn` for keys, or `SerialColumn` when targeting PostgreSQL older than 10. `UseIdentityAlwaysColumns()`, `UseSerialColumns()` and `UseHiLo()` on the model builder change that. - Identity-by-default columns accept explicit key values, which is what lets a data copy keep the clinic's existing patient and appointment ids. PostgreSQL does not advance the identity sequence for explicitly inserted values, so after the copy the sequence must be moved past the highest id, or the next new row collides with an existing key. - The provider assumes PostgreSQL 14 unless `SetPostgresVersion` says otherwise, and it uses that version to decide which SQL features it may generate. - Existing migrations are **provider-specific**: they hold `SqlServer:` annotations and SQL Server store types. Generate a new migration set for PostgreSQL; supporting both databases at once needs separate migration sets. ## SQL hidden in the model Search the model configuration for SQL strings before the switch, because the provider passes them through untouched: - `HasDefaultValueSql("GETUTCDATE()")` must become a PostgreSQL expression such as `now()`; - `HasComputedColumnSql(...)` bodies, check constraints and filtered-index conditions use SQL Server syntax and bracket-quoted identifiers; - raw queries with `FromSql` or `ExecuteSql` need the same review, since `TOP`, `ISNULL` or square brackets do not exist in PostgreSQL. ## Testing the switch Run the integration tests against PostgreSQL itself, not against a fake provider, because every difference above is invisible elsewhere. Pay particular attention to tests that compare strings, sort text or assert times. ## A short checklist 1. Swap the provider package and `UseNpgsql`, setting `SetPostgresVersion` to the real server version. 2. Replace SQL Server store types and functions in the model and queries. 3. Make every `DateTime` written to `timestamptz` UTC. 4. Decide case-insensitive behaviour explicitly: `ILike`, collations or normalized columns. 5. Regenerate migrations and plan the data copy separately.
- Why would you call SetPostgresVersion when the server is PostgreSQL 16?The Npgsql provider assumes version 14 when none is set and decides which SQL features it may use from that number. Telling it the real version lets it generate SQL that relies on newer server features; telling it an older version keeps generated SQL compatible with an older server.
- Can the existing SQL Server migrations be applied to the new PostgreSQL database?No. Each migration and the model snapshot carry SQL Server store types and `SqlServer:` annotations, and the Npgsql migrations generator cannot turn those into PostgreSQL DDL faithfully. Start a new migration set for PostgreSQL and move the data with a separate copy step.
- The SQL Server migrations can be replayed on PostgreSQL once the connection string changes.
- Npgsql converts a Local DateTime to UTC before writing it to timestamptz.
- String comparisons stay case-insensitive because EF Core normalizes them on every provider.
- EF.Functions.ILike works on SQL Server too, since EF.Functions is shared by all providers.
- The Npgsql provider creates serial columns for keys on every PostgreSQL version.