CoreEx.Database 4.2.0

CoreEx.Database

Provides the IDatabase / Database ADO.NET abstraction, DatabaseCommand fluent query builder, DatabaseRecord row reader, multi-result-set support, convention-based column mapping, database wildcard translation, typed mapper contracts, and the transactional outbox relay infrastructure for publishing events from a relational database.

Overview

CoreEx.Database wraps ADO.NET (DbConnection, DbCommand, DbDataReader) with a fluent, OpenTelemetry-instrumented API that integrates tightly with CoreEx entity contracts. Every operation flows through DatabaseCommand, which accumulates a DatabaseParameterCollection, opens the connection on demand, and executes via DatabaseInvoker to emit spans and structured log entries.

The package follows an explicit-mapping philosophy: IDatabaseMapper<TSource, TDestination> implementations hand-write the column-to-property assignments (calling DatabaseRecord.GetValue<T>(columnName) and DatabaseParameterCollection.AddParameter). The DatabaseMapper static utility handles the cross-cutting columns (RowVersion → IETag, TenantId, IsDeleted, CreatedBy/CreatedOn/UpdatedBy/UpdatedOn) so individual mappers only write their entity-specific columns.

The outbox sub-namespace implements the Transactional Outbox pattern: events are written to an outbox table within the same database transaction as the business mutation; the relay hosted service polls the table and publishes to the configured IEventPublisher.

Key capabilities

• 🔌 IDatabase abstraction: IDatabase / Database<TConn> manage connection lifetime, transaction enlistment, DateTimeTransform, DateTimeOffsetTransform, DatabaseColumns naming conventions, and DatabaseWildcard configuration.
• 📝 Fluent command builder: DatabaseCommand accumulates parameters via IDatabaseParameters<T> fluent methods, then executes as non-query, scalar, single-row, first-or-default, collection, or multi-result-set.
• 📖 DatabaseRecord: Wraps DbDataReader with named and ordinal GetValue<T> accessors that apply DateTimeTransform, null-safety, and RowVersion decoding.
• 🗂️ Multi-result-set: SelectMultiSetAsync reads multiple result sets in a single round-trip using IMultiSetArgs / MultiSetSingleArgs<T> / MultiSetCollArgs<T> descriptors.
• 🗃️ Typed mapper contracts: IDatabaseMapper<TSource, TDestination> / IDatabaseMapper<T> define the mapping surface; DatabaseMapper<T> provides an abstract base with MapFromDb / MapToDb override points.
• 🏷️ Standard column mapping: DatabaseMapper.MapStandardFromDb automatically reads RowVersion → IETag, TenantId, IsDeleted, and change-log columns from DatabaseRecord; MapStandardToDb writes them to parameters.
• 🦁 Database wildcard: DatabaseWildcard translates CoreEx WildcardResult selections into database LIKE patterns (%, _) with configurable escape character.
• 📡 DatabaseInvoker tracing: Every command execution is wrapped by DatabaseInvoker (an InvokerBase<IDatabase>) emitting OpenTelemetry spans tagged with command text, operation result, and error details.
• 📤 Transactional outbox relay: DatabaseOutboxRelayBase<TDatabase, TSelf> polls an outbox table, deserializes events, publishes them via IEventPublisher, and marks them as sent — all within a configurable polling hosted service.
• 🔢 SQL statement abstraction: SqlStatement carries CommandText, CommandType (Text or StoredProcedure), and factory methods for inline SQL, embedded SQL resources, and stored procedure calls.

Key types

Transaction isolation

The SQL Server and PostgreSQL IUnitOfWork.TransactionAsync overloads accepting provider arguments honor SqlServerDatabaseArgs.IsolationLevel or PostgresDatabaseArgs.IsolationLevel when creating a root transaction. Overloads without explicit arguments use the database's configured arguments; the default is ReadCommitted. The framework owns commit, rollback and disposal, and EfDb enlists its shared connection through IDatabase.UseTransactionChanged.

Nested calls use savepoints and inherit the parent's isolation level, regardless of their arguments. They do not upgrade isolation or independently commit the parent. An operation needing a particular isolation guarantee must verify an existing parent's level. Isolation semantics are provider-specific: PostgreSQL RepeatableRead provides a stable MVCC snapshot, whereas SQL Server RepeatableRead does not prevent phantoms and is not a substitute for SQL Server Snapshot.

Read-only callbacks need not enqueue events. At the root boundary, a configured outbox publishes any queued events before commit; nested calls leave publication to their parent.

SQL statement source

Prefer the EF Core / EfDb model APIs for ordinary CRUD and LINQ queries. When application code needs custom relational SQL, put the static SQL in a named .sql or .pgsql file and embed it in the Infrastructure assembly, regardless of its length. Load it once with SqlStatement.FromResource<TResource> and execute it through IDatabase.Statement; keeping SQL in a discoverable resource makes it easier to review and extend without mixing it into orchestration code.

private static readonly SqlStatement _isReady =
    SqlStatement.FromResource<ProductSearchIndex>("ProductSearchIsReady.pgsql");

var ready = await _ef.Database.Statement(_isReady)
    .Param("Profile", profile, dbType: DbType.String)
    .ScalarAsync<bool>(cancellationToken)
    .ConfigureAwait(false);

The Infrastructure project must explicitly embed the files (for example, <EmbeddedResource Include="Repositories\Sql\*.pgsql" />). Resource lookup matches the end of the fully qualified resource name, so keep filenames unique within the assembly. Bind values with DatabaseCommand.Param; do not interpolate user or data values into SQL. Use SqlStatement.FromText only when SQL must genuinely be generated at runtime, such as when a validated or safely quoted identifier is dynamic; continue to bind data values as parameters. Keep schema changes in the *.Database project's DbEx migrations, not in runtime query resources. The Products search implementation is a complete example (ProductSearchIndex, search guide).

Namespaces

Related Namespaces

• CoreEx.Data - IUnitOfWork, DataResult, QueryArgsConfig; IDatabaseUnitOfWork extends IUnitOfWork.
• CoreEx.Mapping - Mapper.MapStandardFrom pattern mirrored by DatabaseMapper.MapStandardFromDb for the database layer.
• CoreEx.Wildcards - WildcardResult produced by Wildcard.Parse() is translated to LIKE patterns by DatabaseWildcard.
• CoreEx.Invokers - DatabaseInvoker and DatabaseOutboxRelayInvoker extend InvokerBase for OpenTelemetry tracing.
• CoreEx.Events - IEventPublisher is the outbox relay's publication target; EventData is what the outbox table stores.
• CoreEx.Database.SqlServer - SQL Server-specific Database implementation, SqlServerDatabaseExtensions, and stored-procedure conventions.
• CoreEx.Database.Postgres - PostgreSQL-specific Database implementation and Npgsql extensions.

AI Usage Guide

An AGENTS.md file is included with this package. AI coding assistants (GitHub Copilot, Claude, Cursor, etc.) that support workspace-injected package documentation will automatically surface concise usage guidance, code examples, and Do Not rules for this package without requiring a local CoreEx checkout.