How to use CustomSagaFinder with NServiceBus.Persistence.Sql for SqlServer 2012


I’m trying to create my NServiceBus endpoint using NServiceBus.Persistence.Sql ( it was previously using RavenDB Persistence. The problem is i have the need to use a custom saga finder and we are on SqlServer 2012 without built-in json query capabilities. How can i do this in an efficient way? I’m trying to replace the following query

public Task<MySagaData> FindBy(ILineItemCancelled message, SynchronizedStorageSession storageSession, ReadOnlyContextBag context)

            return context.Get<SynchronizedStorageSession>().RavenSession().Query<MySagaData>()
                .SingleOrDefaultAsync(x => x.LineItems.Any(guid => guid.Equals(message.LineItemId)));

Hi John,

Short answer, you can’t. It requires SQL 2016 and says so right at the top of the page:

My personal opinion: saga finders are a code smell and should be avoided at all costs. They require detailed understanding of how the underlying saga persister operates and often require a reimplementation of the query half of the persistence.

In this case, it looks like ILineItemCancelled should really be made to contain a (guessing here) OrderId as well, and then you wouldn’t need a custom finder at all.