Table of Contents

Class SqlServerProjectionStore<TProjection>

Namespace
Excalibur.EventSourcing.SqlServer
Assembly
Excalibur.EventSourcing.SqlServer.dll

SQL Server implementation of IProjectionStore<TProjection>.

[SuppressMessage("Design", "CA1506:AvoidExcessiveClassCoupling", Justification = "This class sat exactly at the coupling limit, and declaring the destination kind tipped it over. The trade is named rather than hidden: the coupling metric is a smell, and the invariant it is being traded against is that a positioned write cannot reach a transactional destination -- previously guarded only by a CHECK constraint in shipped DDL, reported through a provider exception the positioned contract does not declare. Making that call inexpressible outranks a type count. The oversized coupling is tracked debt, not an endorsement.")]
public sealed class SqlServerProjectionStore<TProjection> : ITransactionalProjectionStore<TProjection>, IProjectionGenerationReaderFactory<TProjection>, IAotProjectionGenerationReaderFactory<TProjection>, IPageableProjectionStore<TProjection>, IPositionedProjectionStore<TProjection>, IProjectionStore<TProjection>, IServiceProvider where TProjection : class

Type Parameters

TProjection

The projection type to store.

Inheritance
SqlServerProjectionStore<TProjection>
Implements
IProjectionStore<TProjection>
Inherited Members

Remarks

Provides projection storage using SQL Server with JSON serialization for projection data. Uses MERGE statement for thread-safe upsert operations and dynamic SQL generation for dictionary-based filter queries.

This class supports two constructor patterns:

  • Simple: Connection string for most users
  • Advanced: Connection factory for multi-database, pooling, or IDb integration

Constructors

SqlServerProjectionStore(Func<SqlConnection>, ILogger<SqlServerProjectionStore<TProjection>>, ITenantContext, string?, JsonSerializerOptions?, string, ProjectionDestinationKind)

Initializes a new instance of the SqlServerProjectionStore<TProjection> class with a connection factory.

public SqlServerProjectionStore(Func<SqlConnection> connectionFactory, ILogger<SqlServerProjectionStore<TProjection>> logger, ITenantContext tenantContext, string? tableName = null, JsonSerializerOptions? jsonOptions = null, string schemaName = "dbo", ProjectionDestinationKind destinationKind = ProjectionDestinationKind.Positioned)

Parameters

connectionFactory Func<SqlConnection>

A factory function that creates SqlConnection instances.

logger ILogger<SqlServerProjectionStore<TProjection>>

The logger instance.

tenantContext ITenantContext

The ambient tenant context. Required: this store partitions rows by tenant, and it resolves that partition from here, so there is no state in which the partition is undecided. A single-tenant host receives the framework default context and operates as the one canonical tenant.

tableName string

Optional table name. Defaults to projection type name.

jsonOptions JsonSerializerOptions

Optional JSON serializer options.

schemaName string

The schema holding the table. Defaults to dbo. Honoured by every statement this store runs: a schema setting the data access does not use reads as supported and does nothing.

destinationKind ProjectionDestinationKind

Which apply path owns this destination. Defaults to Positioned, the narrower claim: a positioned store declines the transaction-enlisted capability, so a destination that should have been transactional fails at the seam that needs it rather than being written through the wrong contract. Pass Transactional for a table provisioned by CreateTransactionalTableScript; that store then declines the positioned capability.

Remarks

This is the advanced constructor for scenarios requiring custom connection management.

SqlServerProjectionStore(string, ILogger<SqlServerProjectionStore<TProjection>>, ITenantContext, string?, JsonSerializerOptions?, string, ProjectionDestinationKind)

Initializes a new instance of the SqlServerProjectionStore<TProjection> class.

public SqlServerProjectionStore(string connectionString, ILogger<SqlServerProjectionStore<TProjection>> logger, ITenantContext tenantContext, string? tableName = null, JsonSerializerOptions? jsonOptions = null, string schemaName = "dbo", ProjectionDestinationKind destinationKind = ProjectionDestinationKind.Positioned)

Parameters

connectionString string

The SQL Server connection string.

logger ILogger<SqlServerProjectionStore<TProjection>>

The logger instance.

tenantContext ITenantContext

The ambient tenant context. Required: this store partitions rows by tenant, and it resolves that partition from here, so there is no state in which the partition is undecided. A single-tenant host receives the framework default context and operates as the one canonical tenant.

tableName string

Optional table name. Defaults to projection type name.

jsonOptions JsonSerializerOptions

Optional JSON serializer options.

schemaName string

The schema holding the table. Defaults to dbo. Honoured by every statement this store runs: a schema setting the data access does not use reads as supported and does nothing.

destinationKind ProjectionDestinationKind

Which apply path owns this destination. Defaults to Positioned, the narrower claim: a positioned store declines the transaction-enlisted capability, so a destination that should have been transactional fails at the seam that needs it rather than being written through the wrong contract. Pass Transactional for a table provisioned by CreateTransactionalTableScript; that store then declines the positioned capability.

Remarks

This is the simple constructor for most users.

Methods

CountAsync(IDictionary<string, object>?, CancellationToken)

Counts projections matching the specified filters.

public Task<long> CountAsync(IDictionary<string, object>? filters, CancellationToken cancellationToken)

Parameters

filters IDictionary<string, object>

Dictionary-based filter conditions. Pass null for total count.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<long>

The count of matching projections.

DeleteAsync(string, CancellationToken)

Permanently deletes a projection key within the store's tenant boundary and physical generation.

public Task DeleteAsync(string id, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

cancellationToken CancellationToken

Cancellation token.

Returns

Task

A task representing the asynchronous operation.

Remarks

This operation is idempotent. Deleting an absent key also records terminal retirement. The store atomically removes projection payload and retains minimal key/deletion metadata. Reads, queries and counts hide that metadata. Later writes cannot recreate the key; conditional writes report Vanished and unconditional writes fail explicitly. To repopulate the key, rebuild into a new physical generation. Retirement metadata must remain while the current generation can receive writes; physical removal is not a reset. This does not erase the source event history or every identifier in retained metadata. Retirement is local to the destination key. It does not cancel handlers already running, discovery of alternate projection keys, or external side effects; those must remain idempotent.

Tenant isolation follows the store's IProjectionTenantBoundary capability. A tenant-partitioned or tenant-routing store retires only the current tenant's key; the same id in another tenant remains unchanged. An estate-wide store has a shared physical key space: ambient tenant context alone does not isolate deletion. Use a tenant-aware store boundary when separate tenants may use the same projection identifier.

GetByIdAsync(string, CancellationToken)

Gets a projection by its unique identifier.

[RequiresUnreferencedCode("JSON serialization and deserialization might require types that cannot be statically analyzed.")]
[RequiresDynamicCode("JSON serialization and deserialization might require runtime code generation.")]
public Task<TProjection?> GetByIdAsync(string id, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<TProjection>

The projection if found; otherwise, null.

Remarks

Within a tenant-partitioned or tenant-routing store, the same id in another tenant is reported as not found. Estate-wide stores share their physical key space.

GetWithPositionAsync(string, CancellationToken)

Reads the projection and the position of the last event folded into it, as one observation.

[RequiresUnreferencedCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
[RequiresDynamicCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
public Task<(TProjection? Projection, ProjectionPosition Position)> GetWithPositionAsync(string id, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<(TProjection Projection, ProjectionPosition Position)>

The projection and its position, read atomically. A retired key returns a null projection with Retired; an ordinary missing key returns null without that marker. Only an existing Positioned row can be advanced from. Existing Unnumbered and Unplaceable rows require a complete rebuild; a retired key rejects all writes within its physical generation.

Remarks

One call, deliberately. Reading the state and the position separately admits a writer between them, after which the position the caller holds certifies a prefix its state does not contain — and the conditional write then accepts, losing every event in the gap. No caller can be correct on a split read, however atomic the write is.

QueryAsync(IDictionary<string, object>?, QueryOptions?, CancellationToken)

Queries projections using dictionary-based filters.

[RequiresUnreferencedCode("JSON serialization and deserialization might require types that cannot be statically analyzed.")]
[RequiresDynamicCode("JSON serialization and deserialization might require runtime code generation.")]
public Task<IReadOnlyList<TProjection>> QueryAsync(IDictionary<string, object>? filters, QueryOptions? options, CancellationToken cancellationToken)

Parameters

filters IDictionary<string, object>

Dictionary-based filter conditions. Pass null for no filtering.

options QueryOptions

Query options for pagination and sorting. Pass null for defaults.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<IReadOnlyList<TProjection>>

The matching projections.

Remarks

Filters use property names as keys with optional operator suffixes:

  • ["Status"] = "Active" - Equality (default)
  • ["Amount:gt"] = 100 - Greater than
  • ["Amount:gte"] = 100 - Greater than or equal
  • ["Amount:lt"] = 1000 - Less than
  • ["Amount:lte"] = 1000 - Less than or equal
  • ["Status:neq"] = "Deleted" - Not equals
  • ["Tags:in"] = new[] { "A", "B" } - In collection
  • ["Name:contains"] = "test" - String contains

Multiple filters are combined with AND logic. Providers translate these filters to their native query syntax (SQL WHERE, MongoDB filters, CosmosDb queries, etc.).

Tenant-partitioned and tenant-routing stores exclude other tenants' projections before applying filters and paging options. Estate-wide stores query their shared destination.

QueryPagedAsync(IDictionary<string, object>?, int, int, QueryOptions?, CancellationToken)

Queries projections with offset-based pagination, returning a page of results along with total count metadata.

[RequiresUnreferencedCode("JSON serialization and deserialization might require types that cannot be statically analyzed.")]
[RequiresDynamicCode("JSON serialization and deserialization might require runtime code generation.")]
public Task<PagedResult<TProjection>> QueryPagedAsync(IDictionary<string, object>? filters, int pageNumber, int pageSize, QueryOptions? options, CancellationToken cancellationToken)

Parameters

filters IDictionary<string, object>

Dictionary-based filter conditions. Pass null for no filtering.

pageNumber int

The 1-based page number to retrieve.

pageSize int

The number of items per page.

options QueryOptions

Query options for sorting. Pass null for default ordering.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<PagedResult<TProjection>>

A PagedResult<T> containing the page items and pagination metadata.

Remarks

Uses a single-roundtrip SQL query with COUNT_BIG(*) OVER() window function to return both the page data and total count in one database call. Falls back to OFFSET/FETCH for pagination.

Exceptions

ArgumentOutOfRangeException

Thrown when pageNumber is less than 1 or pageSize is less than 1.

RebuildAtPositionAsync(string, TProjection, long, CancellationToken)

Overwrites an EXISTING projection's state AND position, for a caller that folded the whole stream from an empty seed.

[RequiresUnreferencedCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
[RequiresDynamicCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
public Task<ProjectionRebuildResult> RebuildAtPositionAsync(string id, TProjection projection, long newPosition, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

projection TProjection

The state, folded from empty over every event at or below newPosition.

newPosition long

The position that state is a complete fold up to.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<ProjectionRebuildResult>

Applied when the row existed and now holds the given state at the given position; Vanished when no row exists, in which case nothing was written and the caller stops.

Remarks

When you are ALLOWED to call this, and it is a property of the CALLER rather than of the stored value. You may call it only when the state you are writing is a fold from an EMPTY seed over every event at or below newPosition. A rebuild satisfies that by construction: it discards whatever was stored and replays the stream. Nothing else does.

Why this exists as its own member. A caller that folded from empty needs no expected position -- there is no prior prefix for its state to be conditional on. That licence used to be borrowed by passing a null expected position to UpsertAtPositionAsync(string, TProjection, long?, long, CancellationToken), which ALSO let callers who had folded onto EXISTING state claim it. Those callers cannot know what prefix the stored state covered, so the position they stamped could assert a prefix the state did not hold -- silently, and indistinguishably from a correct advance. Giving the one legitimate caller its own signature is what makes the illegitimate call inexpressible rather than merely discouraged.

It MUST NOT create the row, exactly as RefoldAtPositionAsync(string, TProjection, long, CancellationToken) must not. An absent row means the projection was DELETED, deletion is how erasure removes personal data, and a rebuild that recreated it would reinstate what the erasure removed -- a whole-stream replay is precisely the write that can reconstruct it. Every implementation refuses retired keys and is UPDATE-only: there is no create arm to avoid taking, and Vanished is how the store reports that it found nothing.

The projection's processor MUST be stopped before you call this. The operation is unconditional on position, so it cannot detect a concurrent advance -- those are the same check, and giving it up is the deliberate price of being able to overwrite a row whose stored number is unusable. It refuses retired keys and reports whether an existing live row was updated. A rebuild already requires a stopped processor, so this states the precondition the caller is already under rather than adding a check that cannot be built.

Exceptions

ArgumentOutOfRangeException

Thrown when newPosition is negative. The negatives are the sentinel space for the states that carry no number, so accepting one would let a caller forge them.

RefoldAtPositionAsync(string, TProjection, long, CancellationToken)

Rewrites a projection's state at the position it ALREADY holds, without advancing it.

[RequiresUnreferencedCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
[RequiresDynamicCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
public Task<ProjectionRefoldResult> RefoldAtPositionAsync(string id, TProjection projection, long atPosition, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

projection TProjection

The re-folded state.

atPosition long

The position the row is expected to hold, and will still hold after.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<ProjectionRefoldResult>

Applied when the state was rewritten; Superseded when the row holds a different position; Vanished when the row is gone; RequiresRebuild when the row's position cannot be matched.

Remarks

When you are ALLOWED to call this, which the name carries because the type system cannot: the events at or below atPosition have CHANGED, so the state no longer matches the prefix the position names. Erasure is the case that produces it — it mutates events in place, so the fold changes while every position stays fixed.

It is not a general "replace the state" operation and must not be used as one. Advancing a projection with new events is UpsertAtPositionAsync(string, TProjection, long?, long, CancellationToken); that operation refuses a non-advancing write precisely to reject a redelivered batch, and this one exists only because that refusal is wrong when the stream beneath the position was rewritten.

atPosition is not nullable. Re-folding "at no position" is meaningless, so the bad call is made inexpressible rather than validated. A row carrying no established position has nothing to match and correctly yields RequiresRebuild.

A re-fold MUST NOT create a row. An absent row means the projection was deleted, deletion is how erasure removes personal data, and recreating it would reinstate what was erased.

Exceptions

ArgumentOutOfRangeException

Thrown when atPosition is negative. Negative values are reserved for storage sentinels, including terminal retirement, and cannot be event positions.

Serialize(TProjection)

Serializes a model with the destination's configured wire format.

[RequiresUnreferencedCode("Projection serialization may use reflection. Use generated metadata instead.")]
[RequiresDynamicCode("Projection serialization may require dynamic code. Use generated metadata instead.")]
public string Serialize(TProjection projection)

Parameters

projection TProjection

Model to persist.

Returns

string

Serialized model.

Serialize(TProjection, JsonTypeInfo<TProjection>)

Serializes a model with generated metadata and the destination's configured wire format.

public string Serialize(TProjection projection, JsonTypeInfo<TProjection> typeInfo)

Parameters

projection TProjection

Model to persist.

typeInfo JsonTypeInfo<TProjection>

Generated type metadata.

Returns

string

Serialized model.

UpsertAsync(string, TProjection, CancellationToken)

Creates or updates a projection, leaving it with NO PLACEABLE POSITION.

[RequiresUnreferencedCode("JSON serialization and deserialization might require types that cannot be statically analyzed.")]
[RequiresDynamicCode("JSON serialization and deserialization might require runtime code generation.")]
public Task UpsertAsync(string id, TProjection projection, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

projection TProjection

The projection to store.

cancellationToken CancellationToken

Cancellation token.

Returns

Task

A task representing the asynchronous operation.

Remarks

This is not a positioned write, and a positioned client must not use it. It replaces the state with a value the store cannot relate to the event stream, so on a store that records positions the row is left Unplaceable: not a fold over any prefix, and therefore something no later positioned write may advance from -- as is a row left Unnumbered, so saying it only of this one would imply the other is advanceable. What separates them is what the row ASSERTS: an unplaceable state is not a fold over any prefix, where an unnumbered one is a complete fold missing only its coordinate. That is recorded explicitly, in the same atomic action that writes the state -- never by silently dropping or resetting the position, which would make a destroyed position indistinguishable from one that was never established.

Calling this on a positioned store makes the projection unadvanceable until it is rebuilt. A positioned write refuses every row carrying no number, so the next batch is refused rather than folded, and the repair is IPositionedProjectionStore<TProjection>.RebuildAtPositionAsync after a whole-stream replay. A caller that has a complete fold but no position NUMBER wants IPositionedProjectionStore<TProjection>.UpsertUnnumberedAsync instead: that row is refused too, but it reads back as a complete fold rather than as a state related to no prefix at all, which is what a reader and an operator each need. Reaching for this method to express that makes the stronger and wrong claim.

Why the specification says this at all. It is this operation, inherited by the positioned sub-interface, that can falsify the fold invariant that sub-interface declares -- so a client holding the base interface can break the subtype's invariant from outside while staying entirely within its rights. Leaving the effect unstated is what let eight implementations agree by accident rather than by contract.

UpsertAtPositionAsync(string, TProjection, long?, long, CancellationToken)

Writes the projection and its new position together, only if the stored position is the one the caller expects and the new position is ahead of it.

[RequiresUnreferencedCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
[RequiresDynamicCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
public Task<ProjectionAdvanceResult> UpsertAtPositionAsync(string id, TProjection projection, long? expectedPosition, long newPosition, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

projection TProjection

The state to store.

expectedPosition long?

The position the caller read. null means the caller believes no projection exists, and MUST lose to a writer that has since created one.

newPosition long

The position of the last event folded into projection.

cancellationToken CancellationToken

Cancellation token.

Returns

Task<ProjectionAdvanceResult>

What the write did, and the position the store holds.

Remarks

The state and the position are written in ONE indivisible action. A store that writes them separately has a window in which a crash leaves a position ahead of its state, and a projection whose position is ahead of its state is silently missing those events forever.

The null case is insert-if-absent and NOTHING ELSE -- a write whose only condition is the ABSENCE of the row. It must never become an unconditional upsert, and it must not match an EXISTING row however that row's own position reads: a caller that read no position knows nothing about which prefix the stored state covers, so claiming one over it is a guess. An existing row is therefore refused -- Superseded when it holds a real position, and Unplaceable when it holds none, whichever of the two no-number states that is. The latter is repaired by RebuildAtPositionAsync(string, TProjection, long, CancellationToken).

Exceptions

ArgumentOutOfRangeException

Thrown when newPosition is negative. Negative values are reserved for storage sentinels, including terminal retirement, and cannot be event positions. Also thrown when expectedPosition is negative. The negatives are the sentinel space for the states that carry no number, so a caller passing one would be naming a sentinel as the position it read -- which is how a refused numberless row becomes adoptable again through a different door. Only ExpectedPositionOrNull produces a legal value here, and it never produces a negative one.

UpsertUnnumberedAsync(string, TProjection, CancellationToken)

Writes a state that is a complete fold over a prefix which has no global position number.

[RequiresUnreferencedCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
[RequiresDynamicCode("Implementations serialize the projection type reflectively; supply JsonSerializerOptions with a source-generated resolver for trimming and AOT.")]
public Task UpsertUnnumberedAsync(string id, TProjection projection, CancellationToken cancellationToken)

Parameters

id string

The projection identifier.

projection TProjection

The folded state.

cancellationToken CancellationToken

Cancellation token.

Returns

Task

A task that completes when the write is durable.

Remarks

This exists so a caller with no position number does not have to reach for the blind surface. The save path folds events that carry no global position yet; its state IS complete, only its coordinate is unknown. Before this member the only way to write that was UpsertAsync(string, TProjection, CancellationToken), which records Unplaceable -- a strictly stronger and wrong claim.

THE ROW THIS WRITES IS A DEAD END UNTIL IT IS REBUILT, and that is the contract rather than a defect. It carries no position, so no positioned write can advance it -- UpsertAtPositionAsync(string, TProjection, long?, long, CancellationToken) refuses it with Unplaceable -- and no re-fold can take it either, because RefoldAtPositionAsync(string, TProjection, long, CancellationToken) matches against a position this row does not have and answers RequiresRebuild. The single way to bring the row back under the guarantee is RebuildAtPositionAsync(string, TProjection, long, CancellationToken), which writes the state and the position together and therefore needs no prior prefix to be conditional on.

So this member suits a caller that has folded a prefix whose events carry no global position numbers yet, and that ACCEPTS the row must later be rebuilt in order to become positioned. What it buys over the blind surface is that the row states honestly what it holds -- a complete fold whose coordinate is unknown, rather than a state related to no prefix at all. That is what lets the stored value be READ as a complete answer while the row waits for its rebuild, and GetWithPositionAsync(string, CancellationToken) is where a caller learns it -- a positioned write refuses both alike and reports only that there was no number to advance from.

The caller states which live state it is producing; the store never infers it. Inferring it would require the store to distinguish creating a row from updating one, in the same atomic action that writes the state -- and four of the document providers cannot do that at all, while the two that can could only do it with a second write whose crash window records a destroyed position as a never-established one. A store that must discriminate create from update to answer this has been asked the wrong question.

WriteAsync(ProjectionTransaction, string, string, string, CancellationToken)

Writes serialized state on the supplied transaction without committing it.

public ValueTask WriteAsync(ProjectionTransaction transaction, string aggregateId, string aggregateType, string json, CancellationToken cancellationToken)

Parameters

transaction ProjectionTransaction

Owning event transaction and source boundary.

aggregateId string

Projection key, equal to the aggregate identifier.

aggregateType string

The one aggregate type this destination belongs to.

json string

Complete model serialized with registered projection metadata.

cancellationToken CancellationToken

Cancellation token.

Returns

ValueTask

A task completed when the enlisted write finishes.