Getting Started
Architecture
NServiceBus
Transports
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

Saga Finder

Component: Sql Persistence
Target Version: NServiceBus 7.x

The SQL Persistence exposes an API to enable the creation of Saga Finders.

Usage

The API is exposed as an extension method on SynchronizedStorageSession and can be called as follows:

Microsoft SQL Server

On Microsoft SQL Server, the saga finder feature requires the JSON_VALUE function that is only available starting with SQL Server 2016.
class SqlServerSagaFinder :
    IFindSagas<MySagaData>.Using<MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, SynchronizedStorageSession session, ReadOnlyContextBag context)
    {
        return session.GetSagaData<MySagaData>(
            context: context,
            whereClause: "JSON_VALUE(Data,'$.PropertyPathInJson') = @propertyValue",
            appendParameters: (builder, append) =>
            {
                var parameter = builder();
                parameter.ParameterName = "propertyValue";
                parameter.Value = message.PropertyValue;
                append(parameter);
            });
    }
}

MySql

class MySqlSagaFinder :
    IFindSagas<MySagaData>.Using<MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, SynchronizedStorageSession session, ReadOnlyContextBag context)
    {
        return session.GetSagaData<MySagaData>(
            context: context,
            whereClause: "JSON_EXTRACT(Data,'$.PropertyPathInJson') = @propertyValue",
            appendParameters: (builder, append) =>
            {
                var parameter = builder();
                parameter.ParameterName = "propertyValue";
                parameter.Value = message.PropertyValue;
                append(parameter);
            });
    }
}

PostgreSql

class PostgreSqlSagaFinder :
    IFindSagas<MySagaData>.Using<MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, SynchronizedStorageSession session, ReadOnlyContextBag context)
    {
        return session.GetSagaData<MySagaData>(
            context: context,
            whereClause: @"""Data""->>'PropertyPathInJson' = @propertyValue",
            appendParameters: (builder, append) =>
            {
                var parameter = builder();
                parameter.ParameterName = "propertyValue";
                parameter.Value = message.PropertyValue;
                append(parameter);
            });
    }
}

Parameters

context

Used to ensure the concurrency metadata is stored in the current session.

whereClause

This text will be appended to a standard Saga select statement:

select
    Id,
    SagaTypeVersion,
    Concurrency,
    Metadata,
    Data
from EndpointName_SagaName
with (updlock)
where 1 = 1

appendParameters

appendParameters allows DbParameters to be appended to the underlying DbCommand that will perform the query.

builder: calls through to DbCommand.CreateParameter to allow construction on a DbParameter.

append: calls through to DbParameterCollection.Add to add the parameter to the underlying DbCommand.

IContainSagaData Construction

Converting the returned information into an IContainSagaData will then be performed by the SQL Persister.

See also SQL Persistence Saga Finder Sample.

Samples