Table of Contents

Class BaseService<TEntity>

Namespace
FishMMO.Database.Npgsql.Services
Assembly
FishMMO-DB.dll

Base class for database services that execute EF Core operations with consistent execution behavior (transactional and read-only), retry behavior for transient failures, and standardized error mapping into DatabaseResult.

public abstract class BaseService<TEntity> where TEntity : class

Type Parameters

TEntity

The entity type this service primarily operates on.

Inheritance
BaseService<TEntity>
Derived
Inherited Members

Remarks

This base type provides three primary execution paths:

  • ExecuteTransactionAsync(Func<Task>, CancellationToken) and ExecuteTransactionAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext, begin an explicit transaction, execute the delegate, then call SaveChangesAsync(CancellationToken) and commit.
  • ExecuteWriteAsync(Func<Task>,string,CancellationToken) and ExecuteWriteAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext, execute the delegate, then call SaveChangesAsync(CancellationToken) without starting an explicit transaction.
  • ExecuteReadAsync(Func<Task>,string,CancellationToken) and ExecuteReadAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) create a fresh NpgsqlDbContext but do not start an explicit transaction and do not call SaveChanges.

Standalone Execution (no ambient scope): A new context is created per attempt to avoid EF change-tracker state leaking across retries. Transient database failures are retried with exponential backoff. Optimistic concurrency conflicts (Version-based authority) and StaleStateException are never retried; they are returned as non-transient failures so the caller can re-read and decide how to proceed.

Ambient Scope Execution (inside an existing Unit of Work): When an operation detects an active FishMMO.Database.Npgsql.Services.DatabaseExecutionScope, it reuses the ambient NpgsqlDbContext and does NOT retry on transient failures. This is by design for the following reasons:

  1. Transaction State Corruption: PostgreSQL aborts the entire transaction on most transient failures. The connection and transaction become unusable, making retry with the same context impossible.
  2. Context State Pollution: The DbContext's change tracker accumulates state. Retrying with a polluted change tracker can cause duplicate key violations or incorrect updates.
  3. Semantic Correctness: If operation A succeeded and operation B failed transiently, retrying B alone without re-evaluating A's preconditions could violate business invariants.

When a transient failure occurs inside an ambient scope, the failure is returned immediately so the caller can restart the entire unit of work with fresh state. Savepoints are used for nested atomicity but cannot recover from connection-level failures.

Constructors

BaseService(INpgsqlDbContextFactory)

Initializes the service and caches the table name for TEntity.

protected BaseService(INpgsqlDbContextFactory contextFactory)

Parameters

contextFactory INpgsqlDbContextFactory

Factory used to create new EF Core contexts.

Exceptions

ArgumentNullException

contextFactory is null.

Fields

ParameterPlaceholderRegex

Rewrites the {0}-style placeholders these services write into raw SQL as Npgsql @p0 parameters.

protected static readonly Regex ParameterPlaceholderRegex

Field Value

Regex

Remarks

Protected rather than private so a service that needs a result-set shape this class does not provide — a multi-row RETURNING, say — can reuse the ambient connection and the same placeholder convention instead of duplicating both.

Properties

DbContextFactory

Gets the factory used to create new NpgsqlDbContext instances.

protected INpgsqlDbContextFactory DbContextFactory { get; }

Property Value

INpgsqlDbContextFactory

MaxPoolSize

Gets the configured maximum pool size for utilization calculations.

protected int MaxPoolSize { get; }

Property Value

int

PerformanceTracker

Gets the query performance tracker used for operation-level timing and slow-query detection.

protected QueryPerformanceTracker PerformanceTracker { get; }

Property Value

QueryPerformanceTracker

PoolMetrics

Gets the connection pool metrics exposed by the current INpgsqlDbContextFactory.

protected ConnectionPoolMetrics PoolMetrics { get; }

Property Value

ConnectionPoolMetrics

RetryPolicy

Gets the retry policy configuration for transient failure handling.

protected RetryPolicyConfiguration RetryPolicy { get; }

Property Value

RetryPolicyConfiguration

TableName

Database table name for TEntity, resolved from EF Core model metadata.

protected string TableName { get; }

Property Value

string

Remarks

Cached at construction time to avoid repeating model metadata lookups on hot paths.

Methods

ExecuteBulkUpsertAsync(NpgsqlDbContext, string, int, object[], string, CancellationToken, BulkVersionConflictPolicy)

Executes a bulk UPSERT statement and enforces version/authority semantics by validating the affected row count.

protected static Task<int> ExecuteBulkUpsertAsync(NpgsqlDbContext dbContext, string sql, int expectedRowsAffected, object[] parameters, string staleStateMessage, CancellationToken cancellationToken, BaseService<TEntity>.BulkVersionConflictPolicy policy = BulkVersionConflictPolicy.Fail)

Parameters

dbContext NpgsqlDbContext

The active DbContext for the current transaction.

sql string

A fully-formed SQL statement (typically using UNNEST + INSERT ... ON CONFLICT DO UPDATE) built with TableName. The SQL should be parameterized for values and must never accept user-controlled identifiers.

expectedRowsAffected int

The number of rows supplied to the statement. Callers should pre-filter inputs (e.g., skip non-active characters) so this expectation is stable.

parameters object[]

SQL parameters to pass to EF Core.

staleStateMessage string

Message used when version/authority is lost.

cancellationToken CancellationToken

Cancellation token.

policy BaseService<TEntity>.BulkVersionConflictPolicy

How to treat rows rejected by version gating.

Returns

Task<int>

The number of rows actually inserted or updated.

Remarks

policy decides what a shortfall means; see BaseService<TEntity>.BulkVersionConflictPolicy. It defaults to Fail so that a caller which has not thought about the question keeps the strictest behaviour.

Why SkipStaleRows exists. Under Fail, one row losing a version race rolls back the entire statement — including every row that would have applied cleanly. For a batch that spans many characters, as the periodic save's does, that turns a routine and expected race into total data loss for everyone in the batch: a single player logging out while the periodic pass is in flight writes their row at a newer version, and the pass then discards the buffs, attributes, abilities and pet state of every other character it had collected. The losing row is not even the one that suffers — its newer data is already safely stored.

What a skip cannot hide. A row is skipped only when the database already holds a version at least as new, so nothing is lost when one is: the stored value is the more recent of the two. The failure mode this would otherwise mask — a version counter that has stopped incrementing, freezing an entity's state silently — still announces itself, because the character row is written through the single-row path, which continues to surface StaleStateException and is incremented from the same counter.

An over-application is always fatal regardless of policy. Affecting more rows than were supplied means the statement matched something it was not given — an ambiguous multi-row join being the usual cause — and that is a defect in the SQL, not a concurrency outcome.

Exceptions

ArgumentNullException

Thrown when dbContext or sql is null.

ArgumentOutOfRangeException

Thrown when expectedRowsAffected is negative.

StaleStateException

Thrown under Fail when fewer than expectedRowsAffected rows were affected, indicating that at least one incoming row was rejected by version gating (e.g., EXCLUDED.version <= table.version).

DatabaseException

Thrown, under any policy, when more rows were affected than were supplied.

ExecuteReadAsync(Func<NpgsqlDbContext, Task>, string?, CancellationToken)

Executes a read-only database operation with a fresh context. Does not create an explicit transaction and does not call SaveChanges. Retries transient failures and maps exceptions into DatabaseResult.

protected Task<DatabaseResult> ExecuteReadAsync(Func<NpgsqlDbContext, Task> action, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task>

The read-only operation to execute.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

A DatabaseResult describing the outcome.

Remarks

Delegates to the generic overload with a unit return value to eliminate code duplication. Use this for queries (Exists/Load/Get/Fetch) to avoid unnecessary transaction overhead. Prefer EntityFrameworkQueryableExtensions.AsNoTracking<TEntity>(Linq.IQueryable<TEntity>) for pure reads.

ExecuteReadAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, string?, CancellationToken)

Executes a read-only database operation with a fresh context and returns a value. Does not create an explicit transaction and does not call SaveChanges. Retries transient failures and maps exceptions into DatabaseResult<T>.

protected Task<DatabaseResult<TResult>> ExecuteReadAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>> action, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task<TResult>>

The read-only operation to execute.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<TResult>>

A DatabaseResult<T> describing the outcome.

Type Parameters

TResult

Result type returned by the operation.

Remarks

Use this for query hot paths. Pair with compiled queries (EF.CompileAsyncQuery) where it helps. Prefer EntityFrameworkQueryableExtensions.AsNoTracking<TEntity>(Linq.IQueryable<TEntity>) for pure reads.

ExecuteReturningAsync<TResult>(NpgsqlDbContext, string, object[], Func<DbDataReader, TResult>, CancellationToken)

Executes a raw SQL statement that produces a result set and maps the first returned row. Use this for non-composable DML statements with RETURNING (INSERT/UPDATE … RETURNING) where exactly one row is expected.

Reuses the ambient EF Core connection and transaction so the call is atomic when invoked inside ExecuteWriteAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken) or ExecuteTransactionAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken).

protected static Task<TResult> ExecuteReturningAsync<TResult>(NpgsqlDbContext dbContext, string sql, object[] parameters, Func<DbDataReader, TResult> map, CancellationToken cancellationToken)

Parameters

dbContext NpgsqlDbContext

The active DbContext providing the connection and ambient transaction.

sql string

Parameterized SQL using {0}, {1}, … placeholders (same syntax as ExecuteSqlRawAsync(DatabaseFacade, string, CancellationToken)). Must never embed user-controlled identifiers.

parameters object[]

Positional parameter values corresponding to the SQL placeholders.

map Func<DbDataReader, TResult>

A delegate that reads one row from the DbDataReader and returns TResult.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<TResult>

The mapped result of the first row.

Type Parameters

TResult

The type produced by the map delegate.

Exceptions

DatabaseException

Thrown when no row is returned.

ExecuteReturningOrDefaultAsync<TResult>(NpgsqlDbContext, string, object[], Func<DbDataReader, TResult>, CancellationToken)

Executes a raw SQL statement that produces a result set and maps the first returned row, or returns default when no row is returned. Use this for non-composable DML statements with RETURNING (INSERT/UPDATE … RETURNING) where zero or one rows are expected.

Reuses the ambient EF Core connection and transaction so the call is atomic when invoked inside ExecuteWriteAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken) or ExecuteTransactionAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken).

protected static Task<TResult?> ExecuteReturningOrDefaultAsync<TResult>(NpgsqlDbContext dbContext, string sql, object[] parameters, Func<DbDataReader, TResult> map, CancellationToken cancellationToken)

Parameters

dbContext NpgsqlDbContext

The active DbContext providing the connection and ambient transaction.

sql string

Parameterized SQL using {0}, {1}, … placeholders (same syntax as ExecuteSqlRawAsync(DatabaseFacade, string, CancellationToken)). Must never embed user-controlled identifiers.

parameters object[]

Positional parameter values corresponding to the SQL placeholders.

map Func<DbDataReader, TResult>

A delegate that reads one row from the DbDataReader and returns TResult.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<TResult>

The mapped result of the first row, or default when no row is returned.

Type Parameters

TResult

The type produced by the map delegate.

ExecuteScalarIntAsync(NpgsqlDbContext, string, object[], CancellationToken)

Executes a raw SQL query that returns a single integer scalar value using ADO.NET, bypassing EF Core entity mapping entirely.

protected static Task<int> ExecuteScalarIntAsync(NpgsqlDbContext dbContext, string sql, object[] parameters, CancellationToken cancellationToken)

Parameters

dbContext NpgsqlDbContext
sql string
parameters object[]
cancellationToken CancellationToken

Returns

Task<int>

ExecuteScalarLongAsync(NpgsqlDbContext, string, object[], CancellationToken)

Executes a raw SQL query that returns a single bigint scalar value using ADO.NET, bypassing EF Core entity mapping entirely.

protected static Task<long> ExecuteScalarLongAsync(NpgsqlDbContext dbContext, string sql, object[] parameters, CancellationToken cancellationToken)

Parameters

dbContext NpgsqlDbContext
sql string
parameters object[]
cancellationToken CancellationToken

Returns

Task<long>

ExecuteTransactionAsync(Func<NpgsqlDbContext, Task>, bool, string?, CancellationToken)

Executes a database operation with a fresh context and transaction. Retries transient database failures. Returns a DatabaseResult with success or failure information.

protected Task<DatabaseResult> ExecuteTransactionAsync(Func<NpgsqlDbContext, Task> action, bool saveChanges = true, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task>

The database operation to execute within the transaction.

saveChanges bool

When true (default), calls SaveChangesAsync(CancellationToken) before committing. Set to false when the operation only uses raw SQL (e.g., ExecuteSqlRawAsync) or when the delegate performs its own SaveChanges.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

A DatabaseResult describing the outcome.

Remarks

Delegates to the generic overload with a unit return value to eliminate code duplication. A new context is created per attempt to avoid EF change-tracker state leaking across retries. StaleStateException is treated as a logical conflict and is not retried.

ExecuteTransactionAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken)

Executes a database operation with a fresh context and transaction, returning a result. Retries transient database failures. Returns a DatabaseResult<T> with success or failure information.

protected Task<DatabaseResult<TResult>> ExecuteTransactionAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>> action, bool saveChanges = true, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task<TResult>>

The database operation to execute within the transaction.

saveChanges bool

When true (default), calls SaveChangesAsync(CancellationToken) before committing. Set to false when the operation only uses raw SQL (e.g., ExecuteSqlRawAsync) or when the delegate performs its own SaveChanges.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<TResult>>

A DatabaseResult<T> describing the outcome.

Type Parameters

TResult

The result type returned by the operation.

Remarks

A new context is created per attempt to avoid EF change-tracker state leaking across retries. This wrapper begins an explicit transaction and always calls SaveChangesAsync(CancellationToken) on success. Prefer ExecuteReadAsync<TResult>(Func<Task<TResult>>,string,CancellationToken) for query-only methods to avoid unnecessary transaction and SaveChanges overhead. StaleStateException is treated as a logical conflict and is not retried.

ExecuteWriteAsync(Func<NpgsqlDbContext, Task>, bool, string?, CancellationToken)

Executes a write database operation with a fresh context. Does not create an explicit transaction, and calls SaveChanges by default. Retries transient database failures. Returns a DatabaseResult with success or failure information.

protected Task<DatabaseResult> ExecuteWriteAsync(Func<NpgsqlDbContext, Task> action, bool saveChanges = true, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task>

The write operation to execute.

saveChanges bool

When true (default), calls SaveChangesAsync(CancellationToken) after action completes. Set to false when the operation does not use EF change tracking (e.g., only ExecuteSqlRawAsync) or when the delegate performs its own SaveChanges.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

A DatabaseResult describing the outcome.

Remarks

Delegates to the generic overload with a unit return value to eliminate code duplication. A new context is created per attempt to avoid EF change-tracker state leaking across retries. StaleStateException is treated as a logical conflict and is not retried.

IMPORTANT: This method does NOT create an explicit database transaction. Each raw SQL statement within the delegate commits independently. For multi-statement atomicity, use ExecuteTransactionAsync(Func<NpgsqlDbContext, Task>, bool, string?, CancellationToken) or wrap calls in a FishMMO.Database.Npgsql.Services.DatabaseExecutionScope.

ExecuteWriteAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken)

Executes a write database operation with a fresh context and returns a value. Does not create an explicit transaction, and calls SaveChanges by default. Retries transient database failures. Returns a DatabaseResult<T> with success or failure information.

protected Task<DatabaseResult<TResult>> ExecuteWriteAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>> action, bool saveChanges = true, string? operationName = null, CancellationToken cancellationToken = default)

Parameters

action Func<NpgsqlDbContext, Task<TResult>>

The write operation to execute.

saveChanges bool

When true (default), calls SaveChangesAsync(CancellationToken) after action completes. Set to false when the operation does not use EF change tracking (e.g., only ExecuteSqlRawAsync) or when the delegate performs its own SaveChanges.

operationName string

Operation name for metrics; defaults to caller member name.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<TResult>>

A DatabaseResult<T> describing the outcome.

Type Parameters

TResult

The result type returned by the operation.

Remarks

A new context is created per attempt to avoid EF change-tracker state leaking across retries. StaleStateException is treated as a logical conflict and is not retried.

IMPORTANT: This method does NOT create an explicit database transaction. Each raw SQL statement within the delegate commits independently. For multi-statement atomicity, use ExecuteTransactionAsync<TResult>(Func<NpgsqlDbContext, Task<TResult>>, bool, string?, CancellationToken) or wrap calls in a FishMMO.Database.Npgsql.Services.DatabaseExecutionScope.

ToJaggedIntArrayJson(IReadOnlyList<int[]>)

Serializes a batch of per-row integer arrays into a compact JSON array-of-arrays string (e.g. [[1,2],[3],[]]) for use with a PostgreSQL jsonb parameter.

protected static string ToJaggedIntArrayJson(IReadOnlyList<int[]> rows)

Parameters

rows IReadOnlyList<int[]>

Returns

string

Remarks

Npgsql 5.x / EF Core 5 cannot reliably bind a ragged (non-rectangular) int[][] value as a native PostgreSQL integer[][] parameter — real PostgreSQL arrays are strictly rectangular, so rows with differing lengths (the normal case for per-character ability/pet event lists) cause Npgsql to mis-infer the parameter's element type. The observed failure mode is a PostgresException (42883, "function ... does not exist") coming from the server misinterpreting the malformed array wire value rather than a clear client-side error.

The fix is to send the batch as JSON text/jsonb instead and decode it server-side (see the UNNEST + jsonb_array_elements(...) WITH ORDINALITY pattern in CharacterAbilityService and CharacterPetService), which sidesteps array parameter type inference entirely.