Custom Saga finding logic when using SQLPersistence

1

I would like to have my saga use a custom saga finder that is based on a message header property.

I found this (Advanced Saga mapping for NServiceBus - Info Support Blog) which is over 4 years old and I am not sure if it is possible to implement this way any longer…

I also saw this (SQL Persistence Saga Finding Logic • Sql Persistence Samples • Particular Docs) but am not sure if $.PaymentTransactionId is a message property or a header property. Plus, I am not on SQL 2016, so do not have the JSON_VALUE function.

Is what I am trying to do possible?

Note: Using NSB 6, RabbitMq and Sql Persistence (sql server)

2

@macdonald-k

PaymentTransactionId is a property on the saga data https://github.com/Particular/docs.particular.net/blob/master/samples/saga/sql-sagafinder/SqlPersistence_3/Shared/OrderSagaData.cs#L11

as for “Is what I am trying to do possible (in non SQL 2016)”.

No i dont think it is possible.

3

I spent 30 minutes or so on it and was able to do it. IFindSagas lets you look into message headers.

Issues I found:

  1. I couldn’t get the ReadOnlyContextBag to return header properties, so I had to cast it to an IMessageHandlerContext. Then I was able to get access to the message headers.
  2. Since JSON_VALUE does not exist in SQL 2014, I had to use SUBSTRING and CHARINDEX to find the value in the Data column. This has poor performance, but, in my case, at most 1 or 2 of these saga’s will be running at a time, so it’s acceptable.
  3. IF I tried to do custom mapping for an IAmStartedBy message, then lots of the properties do not get filled in. These include the property I was mapping on as well as the CorrelationId property for the SagaData table.

I have made the Substring search into a SQL Function.

"@propertyValue = SUBSTRING(Data, CHARINDEX(’“propertyName”: "’, Data) + LEN(’“propertyName”: “’), CHARINDEX(’”’, Data, CHARINDEX(’“propertyName”: "’, Data) + LEN(’“propertyName”: "’)) - CHARINDEX(’“propertyName”: "’, Data) - LEN(’“propertyName”: “’))”,

So it’s all do-able EXCEPT for StartedByMessages; as I do not know of any way to set the CorrelationId, it’s not exposed in the SagaData class.

System.Exception: Failed to ExecuteNonQuery. CommandText:

insert into [NServiceBus].[mySaga]
(
Id,
Metadata,
Data,
PersistenceVersion,
SagaTypeVersion,
Concurrency,
Correlation_OperationId
)
values
(
@Id,
@Metadata,
@Data,
@PersistenceVersion,
@SagaTypeVersion,
1,
@CorrelationId
) —> System.Data.SqlClient.SqlException: Must declare the scalar variable “@CorrelationId”.

4

Hi

As described here you can’t declare a correlation property if none of the messages is mapped to it. Correlation property only makes sense if it is used and mapped to messages. If all your messages use finders you should declare it as null. In that case the insert statement will not contain the @CorrelationId parameter that causes the problem.

Szymon