Table of Contents

Class CharacterService

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 sealed class CharacterService : BaseService<CharacterEntity>, ICharacterService, ICountByKeyAction<string>, IDeleteByKeyVersionedAction<long>, IFetchByKeyAction<long, CharacterData?>, IFetchByKeyAction<string, CharacterData?>, IFetchManyByKeyAction<string, CharacterData>, IPersistAction<CharacterData>
Inheritance
CharacterService
Implements
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

CharacterService(INpgsqlDbContextFactory)

Initializes a new instance of the CharacterService class.

public CharacterService(INpgsqlDbContextFactory dbContextFactory)

Parameters

dbContextFactory INpgsqlDbContextFactory

The database context factory.

Exceptions

ArgumentNullException

Thrown when dbContextFactory is null.

Methods

AnyOnlineAsync(string, CancellationToken)

Checks whether any non-deleted character on the given account is currently online.

public Task<DatabaseResult<bool>> AnyOnlineAsync(string account, CancellationToken cancellationToken = default)

Parameters

account string

The account name.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<bool>>

A DatabaseResult<T> containing true if at least one character is online, false otherwise.

Remarks

A character whose body is running out a combat-logout timer is deliberately NOT counted as online. Its session is still claimed — that is what keeps the body authoritative and stops another server taking it — but the player who owns it must be able to log back in and rejoin it. Counting it here would lock them out of their own character for the whole linger window, which is the opposite of the intent. A genuine second session still blocks, because a live session never carries this flag.

A claim whose lease has expired does not count either. TryClaimAsync(long, long, CancellationToken) treats an expired lease as free — that is the whole recovery path for a scene server that died holding characters — but this check did not, so the row a crashed server left behind reported its owner as permanently online. The account was then refused at login forever with "already online", against a session that no longer existed and a character any server was free to claim. Matching the claim predicate here bounds that to one lease duration.

ClearCombatLoggedAsync(long, CancellationToken)

Clears the combat-logout flag on a character.

public Task<DatabaseResult> ClearCombatLoggedAsync(long characterId, CancellationToken cancellationToken = default)

Parameters

characterId long

Character to clear.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

Remarks

Used when the scene server that held a character's body is judged to be gone, so the character is no longer waiting for a body that will never be handed back.

CountAsync(string, CancellationToken)

Counts items for the given key.

public Task<DatabaseResult<int>> CountAsync(string account, CancellationToken cancellationToken = default)

Parameters

account string
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<int>>

CreateCharacterAsync(CharacterData, CancellationToken)

Creates a new character in the database.

public Task<DatabaseResult<long>> CreateCharacterAsync(CharacterData characterData, CancellationToken cancellationToken = default)

Parameters

characterData CharacterData

The character data to create.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<long>>

A DatabaseResult<T> containing the newly inserted character ID on success, or a failure with AlreadyExists if the name is taken, ValidationError for invalid input, or DatabaseError for unexpected failures.

Remarks

Uses a single-statement SQL insert (CTE-based) with execution strategy wrapping to ensure transient database failures are automatically retried. Character names are stored with a lowercase version for case-insensitive uniqueness.

DeleteAsync(long, long, CancellationToken)

Deletes the entity identified by the given key if incomingVersion is newer.

public Task<DatabaseResult> DeleteAsync(long characterId, long incomingVersion, CancellationToken cancellationToken = default)

Parameters

characterId long
incomingVersion long

The authoritative, monotonic version for this delete operation.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult>

Remarks

Soft Delete:

This performs an atomic soft delete rather than removing data. It renames the character (appending DELETED{GUID}) to free up the original name, sets deleted=true, and stamps the character Version to the incoming authoritative value. Character guild/party memberships are hard-deleted (temporary state). This method does not soft-delete character-owned sub-entities; those entities have independent Version streams.

FetchAsync(long, CancellationToken)

Fetches an entity for the given key.

public Task<DatabaseResult<CharacterData?>> FetchAsync(long characterId, CancellationToken cancellationToken = default)

Parameters

characterId long
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<CharacterData?>>

FetchAsync(string, bool?, CancellationToken) 

public Task<DatabaseResult<CharacterData?>> FetchAsync(string characterName, bool? selected, CancellationToken cancellationToken = default)

Parameters

characterName string
selected bool?
cancellationToken CancellationToken

Returns

Task<DatabaseResult<CharacterData?>>

FetchAsync(string, CancellationToken)

Fetches an entity for the given key.

public Task<DatabaseResult<CharacterData?>> FetchAsync(string characterName, CancellationToken cancellationToken = default)

Parameters

characterName string
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<CharacterData?>>

FetchByAccountAsync(string, bool?, CancellationToken)

Fetches a character by account name with an optional selected filter.

public Task<DatabaseResult<CharacterData?>> FetchByAccountAsync(string accountName, bool? selected, CancellationToken cancellationToken = default)

Parameters

accountName string

The account name.

selected bool?

If provided, filters by the selected status.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<CharacterData?>>

A DatabaseResult<T> containing the character data, or null if not found.

FetchByAccountAsync(string, CancellationToken)

Fetches a character by account name. Returns the first matching character.

public Task<DatabaseResult<CharacterData?>> FetchByAccountAsync(string accountName, CancellationToken cancellationToken = default)

Parameters

accountName string

The account name.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<CharacterData?>>

A DatabaseResult<T> containing the character data, or null if not found.

FetchInWorldCharacterAsync(string, CancellationToken)

Returns the account's character that currently holds a session, if any.

public Task<DatabaseResult<CharacterData?>> FetchInWorldCharacterAsync(string account, CancellationToken cancellationToken = default)

Parameters

account string

Account to inspect.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<CharacterData?>>

The in-world character, or null when the account has none.

Remarks

Unlike AnyOnlineAsync(string, CancellationToken) this counts combat-logout bodies as well, because the caller needs to know a body exists in order to refuse switching away from it. An account may only ever have one character in the world, so at most one row matches.

FetchManyAsync(string, CancellationToken)

Fetches many items for the given key.

public Task<DatabaseResult<IReadOnlyList<CharacterData>>> FetchManyAsync(string account, CancellationToken cancellationToken = default)

Parameters

account string
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<IReadOnlyList<CharacterData>>>

FetchNamesAsync(IReadOnlyList<long>, CancellationToken)

Fetches a character by name with an optional selected filter.

public Task<DatabaseResult<IReadOnlyList<CharacterNameData>>> FetchNamesAsync(IReadOnlyList<long> characterIds, CancellationToken cancellationToken = default)

Parameters

characterIds IReadOnlyList<long>

Characters to resolve. Duplicates and non-positive IDs are ignored.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult<IReadOnlyList<CharacterNameData>>>

A DatabaseResult<T> containing the character data, or null if not found.

Remarks

For labelling rows in lists shown to other players — the dungeon finder's instance list, principally. Projects to ID and name only, and is bounded internally as well as by its caller, because it answers a request a client controls the timing of.

IDs that do not resolve are simply absent from the result rather than being reported; a caller showing a list has to cope with a missing name anyway, since a character can be deleted between the list being built and the names being read.

FetchSelectedCharactersByAccountsAsync(List<string>, int, CancellationToken)

Retrieves the selected character for each of the specified accounts in batches.

public Task<DatabaseResult<IReadOnlyList<CharacterData>>> FetchSelectedCharactersByAccountsAsync(List<string> accounts, int maxBatchSize = 1000, CancellationToken cancellationToken = default)

Parameters

accounts List<string>

List of account names to query.

maxBatchSize int

Maximum number of accounts per database round-trip (500–2500).

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<IReadOnlyList<CharacterData>>>

A list of selected CharacterData, one per account that has a selected character.

FetchUnownedSessionsAsync(IReadOnlyList<CharacterSessionLeaseData>, CancellationToken)

Returns the subset of leases whose sessions the database no longer attributes to the supplied owner — that is, the claims the caller has lost.

public Task<DatabaseResult<IReadOnlyList<long>>> FetchUnownedSessionsAsync(IReadOnlyList<CharacterSessionLeaseData> leases, CancellationToken cancellationToken = default)

Parameters

leases IReadOnlyList<CharacterSessionLeaseData>

Ownership triples the caller believes it holds.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<IReadOnlyList<long>>>

Character IDs no longer owned by the supplied server/token, including rows that have been deleted.

Remarks

RefreshSessionLeasesAsync(IReadOnlyList<CharacterSessionLeaseData>, CancellationToken) reports only how many rows it refreshed, so a short count says a claim was lost without saying which. This resolves that, and is meant to be called only on the short-count path: it is a diagnostic read, not part of the refresh hot path.

A character reported here is being simulated by a server that can no longer persist it. The caller must evict it locally; see PersistOwnedAsync(CharacterData, CharacterSessionLeaseData, CancellationToken).

PersistAsync(CharacterData, CancellationToken)

Persists the provided data.

public Task<DatabaseResult> PersistAsync(CharacterData characterData, CancellationToken cancellationToken = default)

Parameters

characterData CharacterData
cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult>

Remarks

Persists character state only — it deliberately does not touch the session lease. It used to extend the lease for any row whose session was Online, without checking which server was writing, so a stale save from a server that had already released the character extended the new owner's lease. Lease liveness belongs to the ownership operations (TryClaimAsync(long, long, CancellationToken), ReleaseAsync(long, long, Guid, CancellationToken), RefreshSessionLeaseAsync(long, long, Guid, CancellationToken), RefreshSessionLeasesAsync(IReadOnlyList<CharacterSessionLeaseData>, CancellationToken)), all of which verify ownership before writing.

It also does not write selected. That column belongs to the login flow — character creation sets it, SetSelectedAsync(string, long, CancellationToken) moves it — and a gameplay save has no business asserting it. It used to write true unconditionally, so a save replayed after the player had already picked a different character (the retry queue does exactly this after a database hiccup) left two rows marked selected, and the account would enter the world as whichever one the next lookup returned first.

PersistOwnedAsync(CharacterData, CharacterSessionLeaseData, CancellationToken)

Persists a character row, requiring that the caller still holds its session claim.

public Task<DatabaseResult> PersistOwnedAsync(CharacterData characterData, CharacterSessionLeaseData ownership, CancellationToken cancellationToken = default)

Parameters

characterData CharacterData

Snapshot to persist. Its Version must exceed the stored version.

ownership CharacterSessionLeaseData

The claim this server holds, as returned by TryClaimAsync(long, long, CancellationToken).

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

Success when written; Forbidden when the claim is no longer held; StaleState when a newer version is already stored; NotFound when the row is gone.

Remarks

The claim taken by TryClaimAsync(long, long, CancellationToken) gates who may load a character. Without this method nothing gated who may write one: the plain PersistAsync is guarded only by the monotonic Version, so a server whose lease lapsed while it was still running kept saving a character another server had legitimately claimed — and reliably won, because its version counter had been climbing for the whole session while the new owner started again from the persisted row. The claim was advisory on the write path, which is what made a lease lapse corrupting rather than merely untidy.

Ownership is verified in the same statement as the write, so there is no window between checking and writing. A caller that no longer owns the row gets Forbidden and must stop simulating the character rather than retrying — the current owner's state is authoritative, and replaying a stale snapshot over it would destroy exactly the progress this refuses to overwrite.

RefreshSessionLeaseAsync(long, long, Guid, CancellationToken)

Refreshes the session lease for an owned online character.

public Task<DatabaseResult> RefreshSessionLeaseAsync(long characterId, long ownerServerId, Guid ownerToken, CancellationToken cancellationToken = default)

Parameters

characterId long
ownerServerId long
ownerToken Guid
cancellationToken CancellationToken

Returns

Task<DatabaseResult>

RefreshSessionLeasesAsync(IReadOnlyList<CharacterSessionLeaseData>, CancellationToken)

Refreshes the session lease for many owned online characters in a single round trip.

public Task<DatabaseResult<int>> RefreshSessionLeasesAsync(IReadOnlyList<CharacterSessionLeaseData> leases, CancellationToken cancellationToken = default)

Parameters

leases IReadOnlyList<CharacterSessionLeaseData>

Ownership triples to refresh. Invalid entries are skipped.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<int>>

The number of leases actually refreshed.

Remarks

Session liveness must not depend on save throughput. Refreshing one character per round trip inside the periodic save loop meant that on a busy shard with a slow database the characters at the tail of the loop could exceed the lease duration between refreshes and become claimable while still online. This performs the whole population in one statement, so the cost is independent of how many characters are resident.

Each entry is verified against the stored owner server and token, so a server that no longer owns a session silently refreshes nothing rather than extending the current owner's lease.

ReleaseAsync(long, long, Guid, CancellationToken)

Releases an online character back to offline and clears ownership in a single step.

public Task<DatabaseResult> ReleaseAsync(long characterId, long ownerServerId, Guid ownerToken, CancellationToken cancellationToken = default)

Parameters

characterId long
ownerServerId long
ownerToken Guid
cancellationToken CancellationToken

Returns

Task<DatabaseResult>

RollbackChannelSwitchAsync(long, DateTime, CancellationToken)

Restores a channel-switch cooldown claimed by TryBeginChannelSwitchAsync(long, TimeSpan, CancellationToken) for a switch that did not happen.

public Task<DatabaseResult> RollbackChannelSwitchAsync(long characterId, DateTime previousUtc, CancellationToken cancellationToken = default)

Parameters

characterId long

Character whose claim is being released.

previousUtc DateTime

The value returned by the claim, restored as-is.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult>

Remarks

The claim has to be taken before the transfer, because it is the last thing that can refuse the request — but the transfer can still fail after it: the character enters combat during the validation, the connection goes away, or the scene server's main-thread queue rejects the hand-off. Leaving the claim in place then charged a player the full cooldown for a switch they were refused, and answered their retry with "you are travelling too often" on top of the refusal they had already been given.

Restores the exact previous value rather than clearing the column, so a player who genuinely switched moments ago still serves out the remainder of that cooldown. Nothing else writes this column, and a character has one session, so the guard below cannot discard a newer legitimate claim.

SetSelectedAsync(string, long, CancellationToken)

Sets the selected character for an account atomically. Deselects all other characters for the account.

public Task<DatabaseResult> SetSelectedAsync(string account, long characterId, CancellationToken cancellationToken = default)

Parameters

account string

The account name.

characterId long

The character ID to select.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult>

A DatabaseResult indicating success or containing a DatabaseException on failure.

Remarks

Uses a single atomic UPDATE with conditional logic: SET selected = (id = characterId). This ensures all characters for the account are updated in one operation without race conditions. Execution strategy wrapping ensures transient database failures are automatically retried.

TryBeginChannelSwitchAsync(long, TimeSpan, CancellationToken)

Atomically claims a character's channel-switch cooldown window: succeeds and stamps the character only if it has not switched within cooldown.

public Task<DatabaseResult<DateTime?>> TryBeginChannelSwitchAsync(long characterId, TimeSpan cooldown, CancellationToken cancellationToken = default)

Parameters

characterId long

Character attempting the switch.

cooldown TimeSpan

Minimum interval between switches.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<DatabaseResult<DateTime?>>

The timestamp the claim replaced when the switch may proceed, so a caller that then fails to perform the transfer can put it back with RollbackChannelSwitchAsync(long, DateTime, CancellationToken); null when the character is still on cooldown and nothing was stamped.

Remarks

A channel switch releases the character and drops the connection, so the client comes back through the world server on a fresh connection id — very possibly to a different scene server. Any cooldown held in memory is therefore erased by the switch itself, which left the limit applying only to switches that were refused. The character row is the only state that survives the hop.

Check and stamp are one statement so two scene servers cannot both conclude the cooldown has elapsed. Deliberately not version-gated: this is a rate limit, not gameplay state, and it must not lose to — or interfere with — a concurrent save.

TryClaimAsync(long, long, CancellationToken)

Attempts to claim ownership of a character session (Offline → Online).

public Task<DatabaseResult<Guid>> TryClaimAsync(long characterId, long ownerServerId, CancellationToken cancellationToken = default)

Parameters

characterId long
ownerServerId long
cancellationToken CancellationToken

Returns

Task<DatabaseResult<Guid>>

Remarks

A claim is permitted if the character is offline or the previous owner's lease has expired. On success, returns a new session owner token that must be presented for subsequent operations.

UpdatePositionAsync(long, float, float, float, float, float, float, float, CancellationToken)

Updates the position and rotation of a character atomically.

public Task<DatabaseResult> UpdatePositionAsync(long characterId, float x, float y, float z, float rotX, float rotY, float rotZ, float rotW, CancellationToken cancellationToken = default)

Parameters

characterId long

The character ID.

x float

The X coordinate.

y float

The Y coordinate.

z float

The Z coordinate.

rotX float

The rotation X component.

rotY float

The rotation Y component.

rotZ float

The rotation Z component.

rotW float

The rotation W component.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult>

A DatabaseResult indicating success or containing a DatabaseException on failure.

Remarks

Uses atomic UPDATE to set all position and rotation components in one operation. Updates last_saved timestamp automatically. Execution strategy wrapping ensures transient database failures are automatically retried.

UpdateSceneAsync(long, long, string, long, CancellationToken)

Updates the routing information for a character atomically.

public Task<DatabaseResult> UpdateSceneAsync(long characterId, long worldServerId, string sceneName, long sceneHandle, CancellationToken cancellationToken = default)

Parameters

characterId long

The character ID.

worldServerId long

The world server the character is being routed through.

sceneName string

The scene name.

sceneHandle long

The scene handle.

cancellationToken CancellationToken

Token to cancel the operation.

Returns

Task<DatabaseResult>

A DatabaseResult indicating success or containing a DatabaseException on failure.

Remarks

Uses atomic UPDATE to set world_server_id, scene_name and scene_handle in one operation. All three are written together because the Scene Server matches an incoming character against its loaded scene instances on the full (world_server_id, scene_name, scene_handle) tuple — persisting the scene half while leaving world_server_id stale makes that lookup reject the character as mismatched. Updates last_saved timestamp automatically. Execution strategy wrapping ensures transient database failures are automatically retried.