EntitySpaces.ORM.PostgreSQL.NET 2026.11.0

<img src="https://es-banner.paul-netstep.workers.dev?src=nuget_readme" alt="EntitySpaces" width="250" height="126">

EntitySpaces.ORM.PostgreSQL.NET

Part of the modernized EntitySpaces ORM — PostgreSQL data provider. Actively maintained fork with full .NET Framework 4.8 to .NET 10 support.

Supportability

🛠️ Requires EntitySpaces Studio

This package provides the PostgreSQL runtime only — it does not generate code. Entity and Collection classes are generated from your database schema using EntitySpaces Studio, a separate WinForms tool.

• Download the latest MSI from Releases and run it. Requires the .NET 10 Desktop Runtime (x64).
• Older builds stay in EntitySpaces.Studio, tagged -deprecated, for reference only.

Version alignment. Studio 2026.11 and the NuGet packages 2026.11.0 ship as one unit: the templates depend on provider changes from the same release. Do not mix a newer Studio with older packages, or the reverse. The previous Studio build (2026.08) remains available for projects still on packages 2026.8.0.

• Connect to your database under Settings → Connection, then run the Generated and Custom class templates.

New Features

• GENERATED BY DEFAULT AS IDENTITY and GENERATED ALWAYS AS IDENTITY — auto and explicit PK insert supported

• INSERT ... RETURNING consolidated into a single round-trip for all output columns (identity PKs, defaults, concurrency and special columns)

• Tolerant column/property name resolution — the provider accepts both the database column name and the generated property name when deciding whether a column is part of an INSERT/UPDATE and when mapping server-returned values back to the entity. This covers:

  • snake_case → PascalCase conversion: order_id ↔ OrderId, customer_id ↔ CustomerId.
  • Arbitrary divergence between a FK column and the PK column it references: invoice.id referenced by invoicedetail.invoiceid — a column that does not follow the schema's naming pattern and does not split on an underscore.
  • PascalCase-only divergence: invoiceid column with an Invoiceid property.

  No code changes required on your side; the tolerant resolution is applied uniformly to INSERT, UPDATE, DELETE, and server-returned value mapping.

• Single-pass RETURNING with CommandBehavior.SingleRow — the reader is closed cleanly after the first row, keeping the connection usable for the next command in the same esTransactionScope. Eliminates intermittent 25P02 cascades in hierarchical batches.

• StoredProcedure path fully functional — the provider invokes PostgreSQL functions via SELECT * FROM "proc_X"(:args) and reads the returned rowset. See StoredProcedure Path below.

• DELETE enforces optimistic concurrency — the generated proc_<table>Delete now includes the concurrency column in its signature and WHERE clause. A stale Version triggers RAISE EXCEPTION ... USING ERRCODE = 'P0001'; the provider's CheckForConcurrencyException maps P0001 (with the esConcurrencyException message prefix) to esConcurrencyException. Same behaviour as MySQL, SQL Server, and Oracle.

• UPDATE uses RETURNING instead of a second SELECT — the dynamic SQL UPDATE path now emits UPDATE ... SET ... WHERE ... RETURNING ... as a single statement. Npgsql rejects multiple queries combined with Output/InputOutput parameters ("Commands with multiple queries cannot have out parameters"), so the previous shape broke the dynamic update path on entities with server-side special columns. The Insert path already used RETURNING; this makes Update consistent.

• Concurrency exception detection and translation to esConcurrencyException (unique/PK violation, serialization failure, deadlock, lock timeout)

• Automatic APPLY → LATERAL join translation — write OuterApply/CrossApply once, get native LEFT JOIN LATERAL / JOIN LATERAL on PostgreSQL, no code changes needed when switching providers

• Server version auto-detection, cached per connection string — supports multiple PostgreSQL instances of different versions simultaneously

• Neon PostgreSQL (serverless cloud) connection support

• Studio metadata engine now extracts is_generated (computed columns), is_identity / pg_get_serial_sequence (auto-increment), numeric_precision/numeric_scale, and timestamp concurrency detection

• Thread-safe parameter cache — the parameter cache is a ConcurrentDictionary keyed by DataID. No lock contention in multi-threaded environments; the cache is built once per entity type and reused safely across threads.

Fixes

• StoredProcedure save path was non-functional — the provider used CommandType.StoredProcedure, which Npgsql 7/8 translates to CALL, valid only for PROCEDURES. PostgreSQL rejects this with SQLSTATE 42883 when the target is a FUNCTION. Migrated to CommandType.Text with SELECT * FROM "func"(:args) and reading the returned rowset. See StoredProcedure Path.

• Special columns (DateModified, AddedBy, ModifiedBy) now return their own columns — previously all three incorrectly added DateAdded to the RETURNING clause, so the actual server-side values were never propagated back to the entity. This is a copy-paste bug in the INSERT builder that affected every generated entity using server-side auditing columns.

• Server-side special columns no longer sent as parameters on INSERT — the Studio-generated ServerSideText is the column name itself (e.g. "DateAdded"), which is not a valid SQL expression. The provider now excludes these columns from the INSERT and lets the DB DEFAULT populate them, fetching the value back via RETURNING.

• Explicit PK insert on IDENTITY columns — when a user assigns a value to an auto-increment PK, the column is now sent as InputOutput and included in RETURNING, so the entity is marked clean after save and the explicit value is preserved.

• DEFAULT columns not explicitly assigned now correctly send DBNull.Value instead of null — Npgsql raises a parameter exception on null, this is now handled automatically.

• Explicit ROLLBACK removed from error paths — the provider no longer issues ROLLBACK on enlisted connections. On Npgsql 6+ / System.Transactions this could abort the ambient transaction, not just the current command. Transaction rollback is now delegated entirely to esTransactionScope, which is the correct owner. The previous defensive code was actively harmful.

• Type resolution corrected to use dual udt_name + data_type mapping, avoiding mismatches on custom/aliased PostgreSQL types

• Dynamic UPDATE failed on entities with server-side special columns — BuildDynamicUpdateCommand appended a second statement after a semicolon to read the server-generated columns back (UPDATE ...; SELECT "DateModified", "Version" FROM ...). Npgsql raises NotSupportedException: Commands with multiple queries cannot have out parameters when the command carries Output / InputOutput parameters — which it does, for DateModified, ModifiedBy, and Version. Replaced the ; SELECT ... suffix with RETURNING .... The existing ExecuteInsertCommand / MapOutputParameters path already knew how to read columns back from a returned row, so no other change was required.

• DELETE on stale Version was silently dropping the row — the generated proc_XDelete function accepted only PK columns, so the WHERE clause never matched on Version and a stale in-memory copy could delete a row updated by another session. Fixed at three levels: the SP template includes the concurrency column in the function signature and WHERE clause, the provider's BuildStoredProcDeleteCommand sends it as an argument, and the exception translator maps P0001 to esConcurrencyException. The same fix pattern was previously applied to MySQL.

• Dynamic INSERT on tables with user-settable IsConcurrency columns — PostgreSQL IsConcurrency columns are nullable timestamps (invoicedate, paidat, duedate, ...), not server-generated rowversion like on SQL Server. The provider now (a) binds DBNull.Value when the user did not assign a value (Npgsql rejects null on InputOutput parameters with InvalidOperationException: Parameter ':X' cannot be null, DBNull.Value should be used instead), and (b) includes the column in the INSERT VALUES list when the user did assign a value — previously NOT NULL concurrency columns like billing_invoice.invoicedate were silently omitted and rejected by the server with SQLSTATE 23502. RETURNING refreshes the value into CurrentValues in both cases.

• MapOutputParameters no longer NREs on empty CurrentValues — the previous implementation iterated esSmartDictionary.Keys to match parameters to columns by normalized name. Keys throws NullReferenceException when the internal dictionary has never been written to — the exact state of an Added entity with no user-assigned properties (e.g. an empty billing_defaults_probe inserted via INSERT ... DEFAULT VALUES RETURNING ...). ContainsKey was null-safe; Keys is not. Replaced the Keys iteration with metadata lookup (columns.FindByColumnName) and dual-slot writes (column name + property name), mirroring what MapReaderRowToCurrentValues already does on the SP path. No Keys access, no NRE.

• Dynamic UPDATE on tables with IsConcurrency columns — three defects in the same branch of BuildDynamicUpdateCommand:

  1. Parameter value never set — same class of bug as the INSERT path. Npgsql rejects InputOutput parameters with null before the SQL reaches the server.
  2. No AND separator between concurrency WHERE clauses — conncur was concatenated with += and no separator. One concurrency column produced valid SQL; two or more produced ... @p4"col5" IS NOT DISTINCT FROM @p5 ... (syntax error). billing_invoice has six IsConcurrency columns. This bug was pre-existing in the provider, not introduced by the Billing port — it had never been surfaced because the Northwind metadata has exactly one IsConcurrency column.
  3. = @p with @p = NULL never matches in SQL. PostgreSQL timestamps on concurrency columns are NULL in the common case (duedate, lastreminderat, invoicefrommonth, ...), so the default path hit zero rows and threw a spurious esConcurrencyException on a single-user, same-session flow. Replaced with IS NOT DISTINCT FROM (NULL-safe equality, PG 8.2+).

  The combined fix binds the original value with DBNull.Value fallback, sets Direction = Input (the WHERE value is the original — the DB does not modify it, nothing to read back), uses IS NOT DISTINCT FROM, and threads a local conncurAnd separator through the loop. SourceVersion = DataRowVersion.Original was removed (no-op on raw NpgsqlCommand, only effective via NpgsqlDataAdapter).

• IsEntitySpacesConcurrency UPDATE branch fixed preemptively — latent, not exercised by any Billing table (billing_* uses IsConcurrency timestamps, not IsEntitySpacesConcurrency). The same class of defect existed in BuildDynamicUpdateCommand: scomma was not updated after emitting the SET clause (a subsequent modified column concatenated without a comma), the WHERE clause was concatenated without the AND separator (same bug as above), and p.Value was not guarded for null. Applied preemptively; a new BO using IsEntitySpacesConcurrency should be validated against the same scenarios as Billing02 (see Known Limitations below).

⚠️ Known Limitations

Dynamic UPDATE does not persist changes to IsConcurrency columns

BuildDynamicUpdateCommand's first branch excludes IsConcurrency from the SET clause:

if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsEntitySpacesConcurrency))
{
    // add to SET
}

Consequence: if the application modifies invoicedate, duedate, or any other IsConcurrency column on a loaded entity, the change is silently discarded. If the only modification is an IsConcurrency column, the SET clause is empty and the generated SQL is syntactically invalid.

Not exercised by the Billing test suite (the tests modify invoicenumber, total, ispaid, paymentid — never a concurrency column). Deferred to the next cycle; the fix requires two parameters per concurrency column (one for SET with CurrentValues, one for WHERE with OriginalValues) plus corresponding handling in MapOutputParameters.

Workaround: assign the column's new value via a raw UPDATE if needed.

⚠️ Transaction Management

Use esTransactionScope for transactions — not System.Transactions.TransactionScope.

Starting with Npgsql 6.0, opening a connection while an ambient System.Transactions.TransactionScope is active no longer enlists that connection in the transaction automatically. If your application relies on TransactionScope for rollback, the INSERT/UPDATE will run in autocommit mode and the rollback will not revert your data.

The provider participates correctly in EntitySpaces' native esTransactionScope, which is the supported mechanism across all EntitySpaces providers:

using (var scope = new esTransactionScope())
{
    var employee = new Employees { FirstName = "Mike", LastName = "Griffin" };
    employee.Save();

    var product = new Products { ProductName = "Some Gadget" };
    product.Save();

    scope.Complete();   // commit; omit to roll back
}

Nested esTransactionScope instances are supported — the inner scope votes on the outer transaction. Omitting Complete() on any scope that owns the root transaction rolls back everything created inside it.

If you also need System.Transactions interop, keep in mind that EntitySpaces does not currently bridge the two mechanisms automatically.

Connection pool behavior on error: All save and load operations release connections safely. On error, the connection is closed by CleanupCommand (or by CommandBehavior.SingleRow when the RETURNING reader was opened). No explicit ROLLBACK is issued — that responsibility belongs to the transaction scope owner. This avoids the class of bugs where a child insert failure would abort an otherwise recoverable transaction.

⚠️ Hierarchical Save

Hierarchical parent-child saves work correctly on PostgreSQL when using the pattern that EntitySpaces was designed for — adding the child to the parent's collection before calling parent.Save():

var order = new Orders
{
    CustomerId = "ALFKI",
    EmployeeId = 4,
    ShipVia = 1,
    OrderDate = DateTime.Now
};

// Recommended — add to the parent's collection
order.OrderDetailsCollectionByOrderId.Add(new OrderDetails
{
    ProductId = 10,
    UnitPrice = 15.50f,
    Quantity = 5,
    Discount = 0.00f
});

order.Save();   // saves the parent first, then each child with the correct FK

AddNew() works the same way:

var detail = order.OrderDetailsCollectionByOrderId.AddNew();
detail.ProductId = 11;
detail.UnitPrice = 10.00f;
detail.Quantity = 2;
detail.Discount = 0.00f;

order.Save();

The child-initiated pattern (save from the child with UpTo set) is also supported:

var detail = new OrderDetails { ProductId = 1, UnitPrice = 25.00f, Quantity = 3, Discount = 0f };
detail.UpToOrdersByOrderId = order;
detail.Save();   // saves the parent first to obtain the PK, then the child

Atomicity — implicit transaction

esEntity.Save() automatically wraps hierarchical saves in an esTransactionScope when the entity has any pre-saves, post-saves, or post-one-saves (i.e. NeedsTransactionDuringSave() returns true). On any exception during the save phases, the scope is rolled back — the parent insert is reverted along with the failed child. No explicit esTransactionScope is required for the common hierarchical case.

For single-entity saves without hierarchy, no scope is created; the operation is a single SQL statement and therefore inherently atomic.

An explicit esTransactionScope around a hierarchical save is optional but recommended when multiple independent entities must succeed or fail together — the framework's internal scope joins the ambient one transparently via Required semantics.

Non-identity parent PKs

FK propagation from parent to child works identically for auto-increment PKs and user-supplied PKs:

• Auto-increment parent — Orders.OrderId (GENERATED BY DEFAULT AS IDENTITY): the parent is inserted, the generated identity is retrieved via RETURNING, and the FK is written into the child before the child's INSERT.
• User-supplied parent — Customers.CustomerId (VARCHAR(5), no identity): the parent PK is already known before the INSERT, so the FK is available immediately and the parent-child ordering is still preserved for referential integrity.
• Explicit value on IDENTITY parent — Orders.OrderId assigned explicitly by the application: PostgreSQL accepts the explicit value on GENERATED BY DEFAULT AS IDENTITY; the provider routes it as InputOutput and includes it in RETURNING, and the FK is propagated the same way.

Both paths are exercised by the test suite against PostgreSQL 17 and 18, covering all five save patterns: persist-then-add, save-together, AddNew(), child-initiated via UpTo, and collection-initiated via collection.Save().

⚠️ Do not use bidirectional linking. Setting both parent.Children.Add(child) and child.UpToParent = parent causes infinite recursion between the parent and child navigation properties. In practice this manifests as a hang or a StackOverflowException depending on the runtime. This is a limitation of the current EntitySpaces Core, not of this provider. Use one direction — the collection when saving from the parent, or UpTo when saving from the child.

Collection Combine()

esEntityCollection<T>.Combine(source) moves entities from a source collection into a target collection. When the target has fks populated by a parent navigation getter (e.g. Customers.OrdersCollectionByCustomerId), the FK values are written into each combined entity's CurrentValues and marked as modified so they are included in the subsequent INSERT. The operation preserves the RowState of Unchanged entities whose FK already matches, and does not duplicate the FK entry in ModifiedColumns.

var source = new OrdersCollection();
var order = source.AddNew();
order.EmployeeId = 1;
order.OrderDate = DateTime.Now;

var target = customer.OrdersCollectionByCustomerId;
target.Combine(source);
target.Save();   // FK is written and persisted

Cross-provider parity

The fixes documented in this README were originally validated against SQL Server and ported to PostgreSQL without provider-specific changes. The following behaviours are now validated against both PostgreSQL 17 and PostgreSQL 18:

• Hierarchical save for identity and non-identity parent PKs.
• FK propagation across all five save patterns.
• Tolerant column/property name resolution, including divergent FK names.
• Combine() FK propagation and RowState preservation.
• Transaction scope, rollback, and atomicity.
• StoredProcedure path — insert, load, update, delete, collection save, optimistic concurrency.
• Optimistic concurrency enforced on both UPDATE and DELETE through the SP template.
• Dynamic SQL path uses single-statement RETURNING for server-generated columns.
• Concurrency columns (IsConcurrency timestamps) handled correctly on both INSERT and UPDATE, including multiple concurrency columns per table (up to six on billing_invoice).

The SP suite is validated at 159 green tests on two consecutive runs against PostgreSQL 17 and PostgreSQL 18, with no provider-specific behaviour differences.

The Billing domain test suite (ported from SQL Server) accounts for 42 of those tests — Billing00–Billing06 (35 ported) plus Billing08 (7 additional: 6 green + 1 [Ignore]d for the known limitation documented above) — all validated against PostgreSQL 17.11.0 and PostgreSQL 18.6.0, with the pre-existing Northwind suite still green.

StoredProcedure Path

The provider invokes PostgreSQL functions via SELECT * FROM "proc_X"(:args), not via CommandType.StoredProcedure. Npgsql 7/8 translates CommandType.StoredProcedure to CALL, which PostgreSQL rejects with SQLSTATE 42883 when the target is a FUNCTION. The generated procedures use RETURNS SETOF "<table>" and RETURN QUERY SELECT (except Delete, which returns void) so the rowset carries the affected row back to the provider.

Generating the stored procedures

Run the PostgreSQL template in EntitySpaces Studio. It produces one <table>.sql per table plus a consolidated PostgreSQL_ALL.sql. Both must be applied with a client that respects dollar-quoting (psql, pgAdmin, DBeaver, Npgsql direct). Script splitters that break on ; inside $es$ ... $es$ are not supported — they produce truncated statements and spurious 42P13 errors.

Optimistic concurrency

Both proc_XUpdate and proc_XDelete enforce the EntitySpaces concurrency column:

Update increments and guards the row:

UPDATE "<table>"
SET "col" = p_col, "Version" = p_Version + 1
WHERE "id" = p_Id AND "Version" = p_Version;

A stale Version produces 0 affected rows; the function returns no rowset; the provider raises esConcurrencyException.

Delete guards the row:

DELETE FROM "<table>"
WHERE "id" = p_Id AND "Version" = p_Version;

GET DIAGNOSTICS v_rowcount = ROW_COUNT;
IF v_rowcount = 0 THEN
    RAISE EXCEPTION 'esConcurrencyException: stale version on delete'
        USING ERRCODE = 'P0001';
END IF;

The provider translates P0001 (matched by message prefix so unrelated RAISE sites are not misclassified) to esConcurrencyException. A stale Version on DELETE surfaces the same exception type as on UPDATE.

Special columns

DateAdded, DateModified, AddedBy, ModifiedBy are excluded from INSERT and UPDATE parameter lists. Server-side columns are populated by the DB DEFAULT; client-side columns by the framework's own SET logic before the SP call. RETURNING fetches the resulting values.

Required alignment

The provider's SP argument list and the function signature must match. Both are derived from the same column filters:

• INSERT: excludes IsAutoIncrement, IsComputed, special columns.
• UPDATE: excludes IsComputed, special columns.
• DELETE: includes PK columns plus the EntitySpaces concurrency column.

If you extend the SP template or the provider, keep both sides in sync. A signature mismatch produces 42883 at runtime.

Known limitations

• The template matches special columns by hardcoded name (DateAdded, DateModified, AddedBy, ModifiedBy) and the concurrency column by heuristic name (Version, RowVersion, VersionNumber). The Studio metadata engine does not expose these configs to templates. Renamed special or concurrency columns require extending the template.

• DROP FUNCTION IF EXISTS "name"(<types>) only matches a function whose signature matches exactly. When a template change alters the signature (for example the DELETE signature now includes the concurrency column), the old overload remains in the database and the new one is created alongside it. Clean up stale overloads before applying a regenerated PostgreSQL_ALL.sql:

  DROP FUNCTION IF EXISTS "proc_invoiceDelete"(int);
  DROP FUNCTION IF EXISTS "proc_invoicedetailDelete"(int);
  

  Skipping this step causes 42883 (function does not exist) or 42725 (ambiguous function call) at runtime.

Dependency Updates

• Updated System.Configuration.ConfigurationManager from 10.0.13 to 10.0.14
• Maintenance release. No API changes affecting EntitySpaces.

Quick Samples

Load a collection:

var customers = new CustomersCollection();
customers.LoadAll();

foreach (var customer in customers)
{
    Console.WriteLine(customer.CompanyName);
}

Create, update, delete an entity:

var employee = new Employees();
employee.FirstName = "Joe";
employee.LastName = "Smith";
employee.Save();               // Create

employee.LastName = "Doe";
employee.Save();               // Update

employee.MarkAsDeleted();
employee.Save();               // Delete

PostgreSQL connection string:

esProviderFactory.Factory = new EntitySpaces.Loader.esDataProviderFactory();

esConnectionElement conn = new esConnectionElement();
conn.Provider = "EntitySpaces.PostgreSQLClientProvider";
conn.Schema = "public";
conn.ConnectionString = "Host=myserver;Port=5432;Database=mydb;" +
                        "Username=myuser;Password=mypassword;SSL Mode=Require;";
esConfigSettings.ConnectionInfo.Connections.Add(conn);

More usage examples (joins, paging, transactions, and the full Fluent SQL API): see the main EntitySpaces README.

Generating your entity classes

See Requires EntitySpaces Studio above.

If this provider saved you time, you can support its development via PayPal. Feedback is welcome through the 3‑minute roadmap survey.