# Saga Finder

<!-- Version variant: sqlpersistence_8; default: [/persistence/sql/saga-finder.md](/persistence/sql/saga-finder.md) -->



The SQL Persistence exposes an API to enable the creation of [Saga Finders](/nservicebus/sagas/saga-finding.md).


## Usage

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


### Microsoft SQL Server


> [!WARNING]
> On Microsoft SQL Server, the saga finder feature requires the [JSON_VALUE function](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql) that is only available starting with SQL Server 2016.

<!-- snippet: SagaFinder-sqlServer -->

```cs
class SqlServerSagaFinder :
    ISagaFinder<MySagaData, MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, ISynchronizedStorageSession session, IReadOnlyContextBag context, CancellationToken cancellationToken = default)
    {
        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);
            });
    }
}
```

<!-- endsnippet -->


### MySQL

<!-- snippet: SagaFinder-MySql -->

```cs
class MySqlSagaFinder :
    ISagaFinder<MySagaData, MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, ISynchronizedStorageSession session, IReadOnlyContextBag context, CancellationToken cancellationToken = default)
    {
        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);
            });
    }
}
```

<!-- endsnippet -->


### PostgreSQL

<!-- snippet: SagaFinder-postgreSql -->

```cs
class PostgreSqlSagaFinder :
    ISagaFinder<MySagaData, MyMessage>
{
    public Task<MySagaData> FindBy(MyMessage message, ISynchronizedStorageSession session, IReadOnlyContextBag context, CancellationToken cancellationToken = default)
    {
        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);
            });
    }
}
```

<!-- endsnippet -->


### 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:

<!-- snippet: MsSqlServer_SagaSelectSql -->

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

<!-- endsnippet -->


#### appendParameters

`appendParameters` allows [DbParameter](https://msdn.microsoft.com/en-us/library/system.data.common.dbparameter.aspx)s to be appended to the underlying [DbCommand](https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx) that will perform the query.

**builder**: calls through to [DbCommand.CreateParameter](https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.createparameter.aspx) to allow construction on a [DbParameter](https://msdn.microsoft.com/en-us/library/system.data.common.dbparameter.aspx).

**append**: calls through to [DbParameterCollection.Add](https://msdn.microsoft.com/en-us/library/system.data.common.dbparametercollection.add.aspx) to add the parameter to the underlying [DbCommand](https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.aspx).


## 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/saga/sql-sagafinder/index.md).