Get multiple workflow instances by id (IPersistenceProvider)

[jgoelten]

Hi @danielgerlag

In the new release of the workflow-core engine you have added the elastic search engine for filtering against different workflow properties. As a result the GetWorkflowInstances(...) service method on the IPersistenceProvider has been obselated while the GetWorkflowInstance(...) still remains active. In my optionion it makes sense to move all the filtering logic over to elastic serach but I'm missing a method to get multiple workflow instances by id:

Task<IEnumerable<WorkflowInstance>> GetWorkflowInstances(string[] ids)

For example, if we have a set of cached or filtered workflow results or IDs, we have to iterate over GetWorkflowInstance(...) one by one to get the WorkflowInstance DTO. Iterating over all ids is not performant.

Is it possible to provide a service method to retrieve multiple instances by a set of IDs?

[danielgerlag]

You can filter the elastic search index by workflow id

searchIndex.Search("", 0, 10, ScalarFilter.Equals(x => x.Id, "<<your workflow id>>"))

there is no way to query multiple IDs in a single call however.

Could you explain your use case in a bit more detail so I can better understand what you are trying to achieve?

[jgoelten]

As a simple example, let's take a document management system where we have thousands of documents that rely on a defined lifecycle management. The lifecycle management for documents is beeing controlled by your workflow engine and persisted within the 'WF' scheme. On the other side, all content and metadata for each document is persisted in it's own scheme or dedicated database.

Every document starts at a draft stage which is the actual starting point for a new workflow definition. When we start a document's lifecycle, we're starting a new workflow instance and store the document's identifier within the workflow's data object (serialized json / searchable). At the same time, we are also adding the newly created workflow id to the document's data model which allows us to find related workflows from the perspective of a document.

To keep track of all document lifecycles we provide a management dashboard that allows users to visualize all passed, running and pending workflow steps (document stages) for each document. Since we cannot show all at the same time, we are using a paging approach (50 documents per page). Further, documents can be filtered against different criterias which are part of the document model.

This is exactly the point, where my feature request comes in. Since we're using a paging mechanism that is primaraly based on the document model, we cannot use the ElasticsearchIndexer for filtering and paging results. We're applying the filters against the document data model and will get a list of workflow id references. Currently, we're iterating over each workflow id using Task<WorkflowInstance> GetWorkflowInstance(string Id); to get the detailed workflow information from the persistence provider. That's why I asked you about getting DTOs of type 'WorkflowInstance' by multiple workflow instance id's.

The current implementation for retrieving single instances could be easily extended to multi instances. The overall sql query for getting multiple results could be fully executed on the sql server. This would lead into a big performance improvment.

I'm suggesting something like this:

        public async Task<IEnumerable<WorkflowInstance>> GetWorkflowInstances(string[] Id)
        {
            using (var db = ConstructDbContext())
            {
                var uids = Id.Select(i => new Guid(i));
                var raw = await db.Set<PersistedWorkflow>()
                    .Include(wf => wf.ExecutionPointers)
                    .ThenInclude(ep => ep.ExtensionAttributes)
                    .Include(wf => wf.ExecutionPointers)
                    .Where(x => uids.Contains(x.InstanceId))
                    .ToListAsync();

                return raw.Select(r=>r.ToWorkflowInstance());
            }
        }

What is your opinion about providing such an interface on the persistence providers? Even if we could use the elastic search provider for retrieving paged results, we are still missing a lot of detailed workflow information from the WorkflowSearchResult DTO because it is not equal to the WorkflowInstance DTO. To get the full workflow information including its execution pointer details we would have to do it the same way as described above: Extracting all workflow instance ids from the search index result and iterate over the Task<WorkflowInstance> GetWorkflowInstance(string Id); service method to get detailed results.

What is your opinion about this? If anything is unclear from my explanation, please let me know.

[danielgerlag]

in this case... I would look at hooking into the Lifecycle events to publish a denormalized model of the workflow instance directly into your native datastore, then you can query it without affecting the workflow engine, and it would also allow your app and workflow-core to evolve their data models with breaking each other in the future... just provides a de-coupling mechanism.

see the release notes for that feature: https://github.com/danielgerlag/workflow-core/releases/tag/1.6.9
I still need to fully document this feature.

[marc-mueller]

Hi @danielgerlag

I second Jan's request. There are many scenario where we need to access multiple workflow instances as a batch. Often the business logic part contains a reference to a workflow instance and therefore there should be a way to retrieve multiple instances. Looping through a list and get an workflow instance at a time is not efficient.

Duplicating the data based on the lifetime events is in this case not a solution. Why would anyone duplicate the data and create a need to maintain every change in the workflow library in the duplicated data store just for being able to retrieve more than one instance at a time?

Is there a technical reason behind your suggestion for not allowing to query multiple instances by id from the persistence provider? To make that point clear, there is no requirement for any query logic or to be able to perform a search against the persistence provider. There is just a need to get multiple instance by ids at once.

Thanks!
Marc

[danielgerlag]

I don't have any issue with a feature like this... but it would need to be implemented by all the persistence providers, which include

• SQL Server
• PostgreSQL
• SQLite
• MongoDB
• DynamoDB
• Redis

[knoxi]

After discussing with @marc-mueller about this feature, I created a PR for it.

Because the persistence providers SQL Server, PostgreSQL and SQLite build on the EntityFramework percistence provider, only four persistence providers had to be adapted at all.

Unfortunately we have not the possibility to test and verify the functionality of the changed persistence providers DynamoDB (AWS) and Redis.