How to query Sagas stored in SQL Persistence table

Question

I need to query a property of Saga Data class to get a list. It is stored on SqlPersistance table [Data] column as a serialized object. Think about a scenario that my SagaData has a property called UserName, so I want to query every saga related to that user. In a sloppy way, I can query the column content, get the list and can create Saga objects out of the content, by querying like:

SELECT [Id]
      ,[Correlation_TaskId]
      ,[Metadata]
      ,[Data]
      ,[PersistenceVersion]
      ,[SagaTypeVersion]
      ,[Concurrency]
  FROM [myWonderfulDb].[dbo].[MyWonderfulPeristanceTable]
  where JSON_VALUE(Data,'$.Username') = 'arthur'

but I am looking for an elegant way to do it by possibly using NserviceBus API's. There is a SagaFinder implementation described in ParticularSoftware documentation (link: https://docs.particular.net/persistence/sql/saga-finder) but this returns only one object which does not perfectly fit into my scenario.

Here how it is implemented in the documentation:

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);
            });
    }
}

Any ideas appreciated. Thank you!

Why do you need to query the saga data? And why can't it be a direct sql query? It is a database store after all, and you don't need the NSB api for that. — Hadi Eskandari
@HadiEskandari Hi Hadi, yes I can do it directly from the database but I was looking for an Nservicebus way to handle it. — Arthur Cam

Daniel Marbach Daniel Marbach · Accepted Answer · 2020-11-04T08:09:55

We have guidance about querying saga state available at

https://docs.particular.net/nservicebus/sagas/#querying-saga-data

In short, you can query the saga data, there is no out of the box way provided by NServiceBus because we recommend using a different approach instead:

the saga to publish events containing the required data and have handlers that process these events and store the data in one or more read model(s) for querying purposes.

The major points why we don't recommend it is in my viewpoint these two

By exposing the data outside of the safeguards of the business logic in the saga the risk is that the data is not treated as read-only. Eventually, a component tries to bypass the saga and directly modify the data.

Querying the data might require additional indexes, resources etc. which need to be managed by the component issuing the query. Those additional resources can influence saga performance.

How to query Sagas stored in SQL Persistence table

2 Answers