EntitySpaces.ORM.OracleManagedClient.NET 2026.11.0

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

EntitySpaces.ORM.OracleManagedClient.NET

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

Supportability

🛠️ Requires EntitySpaces Studio

This package provides the Oracle 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.

The Oracle stored-procedure template (Oracle.est) also generates the PL/SQL scripts for the Load/Insert/Update/Delete procedures used when SqlAccessType = StoredProcedure. Two constraints apply to Oracle 12c+ schemas that use GENERATED BY DEFAULT AS IDENTITY:

• The identity column must be excluded from the INSERT column list — Oracle assigns the value itself. Including it produces ORA-01400.
• The generated value is retrieved through RETURNING ... INTO rather than a SEQUENCE.NextVal lookup, which only applies to legacy sequence-backed keys.

The template handles both cases automatically. If you have a schema that still uses sequences + triggers, the sequence path is emitted instead and the identity path is skipped.

The template also handles server-side special columns (DateAdded, DateModified, AddedBy, ModifiedBy) on Oracle 12c+ schemas where they are populated by a column DEFAULT or a trigger. See Special columns and auditing below for the exact behaviour.

New Features

• Oracle Cloud (Autonomous Database) connection support via wallet-based TLS mutual authentication
• Tolerant column/property name resolution — the provider accepts both the physical 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:
  • Oracle's UPPERCASE folding of unquoted identifiers: INVOICEID ↔ Invoiceid, ID ↔ Id.
  • UPPERCASE + underscore legacy schemas: EMPLOYEE_ID ↔ EmployeeId.
  • 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.
  • No code changes required on your side; the tolerant resolution is applied uniformly to INSERT, UPDATE, DELETE, stored-procedure parameter binding, and server-returned value mapping.
• Stored procedure path support — Load/Insert/Update/Delete through PL/SQL procedures works end-to-end, including hierarchical saves with FK propagation. Parameter names are resolved through the generated property names so Oracle's case-sensitive OracleParameterCollection no longer misses them.
• Server-side special columns — the four EntitySpaces auditing columns (DateAdded, DateModified, AddedBy, ModifiedBy) are populated by the DB (via column DEFAULT or a trigger) and returned to the entity through RETURNING ... INTO on the SP path, or through the RETURNING clause on the dynamic SQL path. The INSERT statements do not include these columns in the column list, so the DB-provided value is not overwritten by a NULL placeholder.
• ODP.NET native type mapping — values returned by OUT parameters as ODP.NET native types (OracleDecimal, OracleTimeStamp, OracleTimeStampTZ, OracleTimeStampLTZ, OracleDate, OracleString, OracleBinaryFloat, OracleBinaryDouble) are converted to their CLR equivalents before being written into the entity's CurrentValues. Without this the entity's typed getters (GetSystemDateTime, GetSystemDecimal, …) would throw InvalidCastException on the raw native value.
• Concurrency exception detection — custom stored-proc errors, unique constraint violations, deadlocks, and serialization failures all translate to esConcurrencyException
• Connection pool safety — hasError flag + ROLLBACK in finally, preventing aborted-transaction connections from being reused
• Oracle Identity columns (GENERATED BY DEFAULT AS IDENTITY, 12c+) detected via ALL_TAB_COLUMNS.IDENTITY_COLUMN
• NUMBER(p,s) correctly mapped to decimal in generated C# code for monetary/high-precision values
• Navigation properties (UpTo) generated from FK metadata in ALL_CONSTRAINTS / ALL_CONS_COLUMNS
• Thread-safe parameter cache — upgraded from a locked Dictionary to a lock-free ConcurrentDictionary, improving throughput under concurrent load
• .NET 8 compatibility — explicit ConfigurationManager.OpenExeConfiguration call added, required because .NET 8 no longer auto-binds the oracle.manageddataaccess.client app.config section
• Studio metadata engine rewritten to use ODP.NET Managed instead of the legacy OLE DB driver
• Empty Entity Insert Support — the provider correctly handles inserting entities with no user-set values. The dynamic SQL builder replaces the unsupported DEFAULT VALUES syntax with (first_col) VALUES (DEFAULT), allowing DB-generated defaults (like SYSTIMESTAMP) to fire and be captured via RETURNING INTO.
• Explicit PK Insert on IDENTITY columns — when a user assigns a value to an auto-increment PK, the column is sent as InputOutput and included in the RETURNING clause. The entity is marked clean after save, bypassing the sequence generator.

Fixes

• Tolerant column/property resolution for FK name divergence — Oracle folds unquoted identifiers to UPPERCASE, so the physical column name (INVOICEID) diverges from the property name emitted by Studio (Invoiceid). The framework writes under both keys depending on the code path — typed property setters use the column name, while ApplyPostSaveKeys uses the property name. The provider previously looked only under the column name, so hierarchical FK propagation silently dropped the value or threw KeyNotFoundException. The provider now resolves by either spelling across the parameter cache, the command builders, and the post-execution mapping.
• Stored procedure parameter lookup by property name — BuildStoredProcInsertCommand and BuildStoredProcUpdateCommand looked up parameters as cmd.Parameters["p" + col.Name], missing the parameter that PopulateStoredProcParameters registered as p<PropertyName>. For columns where metadata diverges (ID vs Id, INVOICEID vs Invoiceid), Oracle's case-sensitive OracleParameterCollection returned null and the follow-up assignment threw NullReferenceException.
• cmd.Dispose() ordering in the SP entity save path — the command was disposed inside the finally block, before the Output/InputOutput parameters were read back into the entity. Dispose releases the parameter collection, so the generated primary key never reached the entity. Dispose is now called after the parameter mapping loop. The hasError + ROLLBACK pattern in the finally block is unchanged — it is still required by ODP.NET Managed to release aborted transactions back to the pool.
• Parameter direction resolution by SourceColumn — the SP command builders flipped Output / InputOutput parameter directions through cmd.Parameters["p" + col.PropertyName], which returned null silently for the concurrency column (Version) on some driver versions. The parameter stayed as Input and the SP received NULL where it expected the current version token, producing ORA-20101 NO RECORDS WERE UPDATED on every UPDATE. The lookup now iterates cmd.Parameters and matches each parameter to its esColumnMetadata through SourceColumn (a deterministic field set by Cache.GetParameters), which cannot miss.
• ODP.NET native type conversion — OUT parameter values for TIMESTAMP, DATE and NUMBER columns arrived as ODP.NET native types (OracleTimeStamp, OracleDate, OracleDecimal, OracleString) that do not implement IConvertible. The entity getters threw InvalidCastException when converting them. The conversion is now centralized in MapOutputValue and applied uniformly across the four save paths (SP entity, SP collection, dynamic entity, dynamic collection).
• Spurious esConcurrencyException in SaveStoredProcCollection — ODP.NET returns -1 as the affected-row count for DML executed through a PL/SQL block (which is how every EntitySpaces stored procedure is structured). The collection save path did not normalize the negative value the way SaveStoredProcEntity, SaveDynamicEntity and SaveDynamicCollection already did, so every successful SP insert threw esConcurrencyException("Update failed to update any records"). Normalization is now applied consistently.
• Tolerant FK lookup in SaveStoredProcCollection.PreprocessParameters — the input-parameter binding for collection saves looked up values by param.SourceColumn only. FK values propagated through ApplyPostSaveKeys → SetProperty are stored under the property name (Invoiceid) rather than the physical column name (INVOICEID), so the lookup missed and bound DBNull, producing ORA-01400 on the child's NOT NULL FK. The lookup now falls back to the metadata's PropertyName when the column-name slot is empty.
• Removed TimeStamp → InputOutput heuristic in PopulateStoredProcParameters — the previous heuristic assumed an Oracle 11g trigger-based flow where server-side timestamps were written back to the client through the SP's OUT parameter. On Oracle 12c+ with column DEFAULT SYSTIMESTAMP, the template declares those parameters as IN and the heuristic caused PLS-00306 wrong number or types of arguments on every SP call. Parameter direction is now decided exclusively by the callers (BuildStoredProc*Command) based on the column's metadata flags.
• Dynamic path no longer concatenates the ServerSideText value into PL/SQL — BuildDynamicInsertCommand and BuildDynamicUpdateCommand used to emit :DateAdded := DateAdded; inside the anonymous PL/SQL block, using the metadata's DateAdded.ServerSideText value (which is the bareword column name, not a SQL expression). Oracle rejected this with PLS-00201 identifier must be declared. Special columns are now excluded from the dynamic INSERT/UPDATE column list and their values are retrieved through the RETURNING clause.
• Oracle stored-procedure template fixes — the code-generation template (Oracle.est) was emitting DDL that fails on 12c+ schemas. Four issues are now fixed:
  • Empty schema prefix ""."proc_X" no longer emitted when table.Schema is empty — this produced ORA-01741: zero-length identifier.
  • Identity columns no longer include a SELECT <seq>.NextVal preamble — identity columns have no sequence; the generated value is retrieved through RETURNING ... INTO.
  • Identity columns are excluded from the INSERT column list and VALUES list — including the column with a NULL placeholder produced ORA-01400.
  • RETURNING ... INTO clause is now emitted for identity columns so the value reaches the OUT parameter the provider reads back.
  • Concurrency preamble uses the PascalCase parameter name (pConcurrency := p<Prop>) rather than col.Name, which could diverge from the declared parameter name.
  • Removed a duplicated / terminator at the end of the Insert block.
  • Server-side special columns (DateAdded, DateModified, AddedBy, ModifiedBy) are declared OUT in the INSERT and included in the RETURNING clause; they are excluded from the INSERT column list and VALUES list so the DB DEFAULT populates them.
  • The UPDATE block no longer declares special columns as parameters and no longer includes them in the SET clause. DateAdded and AddedBy are never touched by an update; DateModified is refreshed server-side via SYSTIMESTAMP.
• CreateConnection() now correctly returns an OracleConnection — it previously returned OleDbConnection, which caused silent failures
• The Delimiters class was corrected to use single quotes for string literals and double quotes for identifiers — both were previously set to double quotes, which caused ORA-00911 (invalid character) on string comparisons
• Documented the correct cleanup pattern for TransactionScope with ODP.NET Managed (which doesn't support DTC/MSDTC) — use TransactionScopeOption.Suppress or the provided RunWithoutTransaction helper to avoid "transaction within transaction" errors during error cleanup
• Dynamic INSERT Empty Entity (DEFAULT VALUES) — Oracle rejects the INSERT INTO table DEFAULT VALUES syntax. The provider now emits INSERT INTO table (first_col) VALUES (DEFAULT) RETURNING ..., fixing ORA-00926 on empty entity saves.
• Concurrency RowCount Enforcement (UPDATE & DELETE) — Oracle does not throw an error when an UPDATE or DELETE affects 0 rows, which caused silent data loss and missed concurrency conflicts. The provider now injects IF SQL%ROWCOUNT = 0 THEN Raise_application_error(-20101, 'NO RECORDS WERE UPDATED/DELETED'); into the PL/SQL blocks. CheckForConcurrencyException translates this to esConcurrencyException.
• NULL-Safe Concurrency for TIMESTAMP columns — IsConcurrency columns (like invoicedate) are nullable. The dynamic UPDATE WHERE clause now uses DECODE(col, param, 1, 0) = 1 (or OR IS NULL logic) to match NULL values correctly, preventing false-positive concurrency exceptions.
• TIMESTAMP Precision Truncation — DateTime.Now in .NET has a precision of 100ns, but Oracle TIMESTAMP only stores microseconds (6 decimal places). The provider now truncates the C# ticks to microseconds before building the concurrency WHERE clause, eliminating ORA-20101 false positives on save.
• Special Column Exclusion from UPDATE WHERE — server-managed auditing columns (DateModified, DateAdded) are now excluded from the dynamic UPDATE concurrency WHERE clause to avoid precision mismatch false positives.

⚠️ Transaction Management

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

ODP.NET Managed does not support Distributed Transaction Coordinator (DTC/MSDTC). An ambient System.Transactions.TransactionScope with TransactionScopeOption.RequiresNew causes "transaction within transaction" errors when any cleanup code inside a finally block tries to open a new connection or save data. This is a driver limitation, not an EntitySpaces limitation.

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.

Connection pool behavior on error: All save and load operations release connections safely. On error, the connection is explicitly rolled back by the provider (hasError flag + ROLLBACK in finally) before being returned to the pool. This is required on ODP.NET Managed — without an explicit ROLLBACK, an aborted Oracle transaction remains in the ABORTED state and any subsequent command on the same pooled connection fails with ORA-08177 or a spurious ORA-00001. The pattern is the opposite of the PostgreSQL and MySQL/MariaDB providers, where the same code was actively harmful because it aborted the ambient transaction owned by the scope.

Testing with TransactionScope: tests that need to run cleanup outside any ambient transaction can use the TransactionScopeOption.Suppress helper, or the RunWithoutTransaction method that the test base class provides. Both are documented in the test suite.

⚠️ Hierarchical Save

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

var customer = new Customer
{
    Id = "ALFKI",
    CompanyName = "Around the Horn"
};

customer.OrderTopCollectionByCustomerId.Add(new OrderTop
{
    EmployeeId = 1,
    OrderDate = DateTime.Now,
    Freight = 25.00m
});

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

AddNew() works the same way:

var order = customer.OrderTopCollectionByCustomerId.AddNew();
order.EmployeeId = 1;
order.OrderDate = DateTime.Now;

customer.Save();

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

var order = new OrderTop { EmployeeId = 1, OrderDate = DateTime.Now };
order.UpToCustomerByCustomerId = customer;
order.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.

Non-identity parent PKs

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

• Auto-increment parent — Invoice.Id (GENERATED BY DEFAULT AS IDENTITY): the parent is inserted, the generated identity is retrieved via RETURNING ... INTO, and the FK is written into the child before the child's INSERT.
• User-supplied parent — Customer.Id (VARCHAR2(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 — Invoice.Id assigned explicitly by the application: Oracle accepts the explicit value on GENERATED BY DEFAULT AS IDENTITY; the provider routes it and the FK is propagated the same way.

All three paths are exercised by the test suite against Oracle 19c, covering all five save patterns: persist-then-add, save-together, AddNew(), child-initiated via UpTo, and collection-initiated via collection.Save().

Special columns and auditing

EntitySpaces supports four server-side special columns that follow the naming convention DateAdded, DateModified, AddedBy, ModifiedBy. On Oracle, when they are configured as ServerSide in the generated metadata (the default for these four names), their values are provided by the database, not by the client:

The provider never sends these columns as input values. They are excluded from the INSERT column list and from the UPDATE SET clause. On the SP path they are declared as OUT parameters and returned to the client through RETURNING ... INTO; on the dynamic SQL path they are returned through the RETURNING clause appended to the INSERT statement. In both cases the DB-assigned values are written back into the entity's CurrentValues, so the typed getters return the correct timestamp after Save().

Consequence for schemas without a DEFAULT or trigger: if a NOT NULL special column has neither a DEFAULT nor a trigger nor an event handler, the INSERT will raise ORA-01400 cannot insert NULL. Add a DEFAULT SYSTIMESTAMP to timestamp columns and, for AddedBy / ModifiedBy, either a trigger or an AddedByEventHandler on the generated entity class.

The behaviour is validated by the test suite for both the SP path and the dynamic SQL path against a schema with DEFAULT SYSTIMESTAMP on both timestamp columns.

Concurrency token (Version and IsConcurrency columns)

EntitySpaces handles optimistic concurrency in two ways on Oracle:

1. The Version token (IsEntitySpacesConcurrency) The concurrency token is the column marked IsEntitySpacesConcurrency in the generated metadata.

• On INSERT the SP sets pVersion := 1. The dynamic path includes the Version column with a hardcoded 1.
• On UPDATE the SP increments the column ("Version" = "Version" + 1) and adds AND "Version" = pConcurrency to the WHERE clause. The dynamic path does the same. If SQL%ROWCOUNT <> 1 the provider raises -20101, translated to esConcurrencyException.
• On DELETE the SP matches both the PK and the concurrency token. A stale token produces 0 affected rows and the same error.

2. Timestamp tokens (IsConcurrency) Columns like invoicedate or duedate can be marked IsConcurrency.

• The dynamic UPDATE builder uses NULL-safe equality (DECODE) to ensure null timestamps match correctly.
• It also truncates C# DateTime ticks to microseconds to prevent false positives against Oracle's TIMESTAMP precision.
• 0-row updates trigger ORA-20101, which is translated by CheckForConcurrencyException.

On a concurrency failure, the entity is left IsDirty = true so the caller can inspect the stale state and retry.

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. Customer.OrderTopCollectionByCustomerId), 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 OrderTopCollection();
var order = source.AddNew();
order.EmployeeId = 1;
order.OrderDate = DateTime.Now;

var target = customer.OrderTopCollectionByCustomerId;
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 PostgreSQL, then ported to Oracle without provider-specific changes to the Core. The following behaviours are validated against Oracle 19c:

• 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 (INVOICEID vs Invoiceid).
• Combine() FK propagation and RowState preservation.
• Stored-procedure path for Load/Insert/Update/Delete.
• Special-column round-trip on both the SP path and the dynamic SQL path.
• Concurrency token semantics (start at 1, increment on UPDATE, esConcurrencyException on stale token).
• Transaction scope, rollback, and atomicity.

🚧 Future work

The following areas are known gaps that are not yet covered by the Oracle provider or the shared test suite. They are tracked for future sessions:

• Custom fields in DynamicQuery. The Select overloads that project literals, expressions, aggregate functions, or ad-hoc SQL columns onto an entity's dynamic dictionary are not covered by the Oracle test suite. This feature spans every provider and needs to be validated consistently across SQL Server, PostgreSQL, MySQL/MariaDB and Oracle before it can be advertised as supported.
• AddedBy / ModifiedBy event handlers. When the generated entity declares UseEventHandler = true for either auditing column, the provider delegates to the AddedByEventHandler / ModifiedByEventHandler on the entity. This path is documented but not exercised by the current test suite — the current Northwind test schema uses UseEventHandler = false.

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

Oracle connection string:

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

esConnectionElement conn = new esConnectionElement();
conn.Provider = "EntitySpaces.ORM.OracleManagedClient.NET";
conn.ConnectionString =
    "Data Source=(description=(retry_count=20)(retry_delay=3)" +
    "(address=(protocol=tcps)(port=1522)(host=your-host.oraclecloud.com))" +
    "(connect_data=(service_name=your_service_name)));User Id=ADMIN;Password=mypassword;";
esConfigSettings.ConnectionInfo.Connections.Add(conn);

Stored procedure mode:

var invoice = new Invoice { Blablabla = "Sample" };
invoice.Save(esSqlAccessType.StoredProcedure);   // uses proc_INVOICEInsert

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.