Dapper: DapperKeyValueStore ignores KeyValueFilter.Take and OrderByKey, so every outbox poll reads the whole outbox and builds an unbounded IN list (SQL Server stalls at about 2,100 pending items) #270

Suggested milestone: 3.10. Affects all Dapper providers.
Related: #265 (Dapper conformance suite), #263 (the KeyValues table, fixed by #266 / #267).

Summary

Core 3.8 (elsa-core #7517, 2026-05-22) added two fields to KeyValueFilter:

• Take (null means no limit, 0 means no rows);
• OrderByKey (order by the persisted key, Id, before Take is applied).

KeyValueFilter.Apply honours both. That is why the EF Core, Memory and MongoDb stores get them for free: they all build their queries through filter.Apply.

DapperKeyValueStore.ApplyFilter builds its SQL by hand and never reads either field. This is the same on elsa-extensions release/3.9.0 and main, in the #266 / #267 fix branches, and in the imported copy under src/extensions/persistence on elsa-core main:

// #266 / #267 version
if (filter.StartsWith) query.StartsWith("Id", true, filter.Key);
else query.Is("Id", filter.Key);
query.In("Id", filter.Keys);
// no ORDER BY, no LIMIT/FETCH

The only built-in consumer that sets these fields is the default transactional outbox store, KeyValueWorkflowDispatchOutboxStore in core 3.8+. It is used when WorkflowDispatcherOptions.UseTransactionalOutbox = true (the default is false). It is polled every 10 s by ProcessWorkflowDispatchOutboxRecurringTask, and also after every commit that wrote outbox items (ProcessOutboxAfterCommit, default true). Each FindManyAsync(batchSize) (OutboxProcessorBatchSize, default 100) runs three key-value reads:

1. Index: prefix with OrderByKey, Take = batchSize, then Keys = <item key of every index row>;
2. Recovery: prefix, the same way;
3. the legacy scan: the Elsa:WorkflowDispatchOutbox: prefix with OrderByKey, Take = batchSize + 1.

On Dapper, each of these returns every matching row. That causes three problems.

• Every poll reads the whole outbox. The index read returns all pending items, so the Keys lookup asks for all of them. The legacy scan returns every outbox row (items, index, index-by-id). The processor then sorts everything in memory and throws away all but 100 items.
• The key list becomes one SQL parameter per key. ParameterizedQueryBuilderExtensions.In emits Id in (@Id0, @Id1, …) with one parameter per key, plus @TenantId for a named tenant. With Take ignored, the key count equals the number of pending items. SQL Server rejects requests with more than 2,100 parameters (about 2,098 usable through sp_executesql). The FindManyAsync call is not inside the processor's per-item try/catch, so once roughly 2,100 items are pending, every poll throws and nothing is dispatched any more. The backlog can then only grow, because draining it is exactly what fails. The same failure happens at SQLite's 32,766-variable limit and at Npgsql's 65,535-parameter limit.
• Every poll writes again. The legacy scan's completion check is rows < batchSize + 1. When all rows come back, the scan never counts as complete, so the per-tenant LegacyScanCompleted marker is never written. With no ordering, the scan also sees Items: rows, which IsRecoverableItemRecord treats as legacy items, and it re-saves up to batchSize of them, every poll.

Repro (fresh migrated SQLite)

On unpatched release/3.9.0 / main, every key-value call fails first with no such table: KeyValues (#263). This issue therefore shows up once #266 / #267 land. The repro below runs on the #266 head, 8b59c59 (Elsa 3.9 previews).

1. Migrate a fresh SQLite file with the Elsa.Persistence.Dapper.Migrations assembly. Then call AddElsa(e => { e.UseDapper(...); e.UseWorkflowManagement(m => m.UseDapper()); e.UseWorkflowRuntime(r => r.UseDapper()); }) in the default tenant.
2. Save N items through KeyValueWorkflowDispatchOutboxStore.SaveAsync, each with a distinct CreatedAt.
3. Call FindManyAsync(100) twice, the same call the processor makes. Count the rows the key-value store returns per call and the writes it performs.

Result per poll (the same on poll 1 and poll 2):

Calling the store directly with FindManyAsync(new KeyValueFilter { Key = "Elsa:WorkflowDispatchOutbox:Index:", StartsWith = true, OrderByKey = true, Take = 3 }) gives:

• Memory: 3 rows, starting with the lowest key;
• Dapper: 250 or 2,200 rows (all of them), in an arbitrary order, starting with …:o00200.

The SQL Dapper executed (captured from the command):

-- index read: no ORDER BY, no LIMIT
select * from KeyValues where 1=1
and (TenantId is null or TenantId = '')
and Id like @IdStartsWith

-- item lookup: one parameter per pending item (250 here, 2,200 in the second run)
select * from KeyValues where 1=1
and (TenantId is null or TenantId = '')
and Id in (@Id0, @Id1, @Id2, …, @Id249)

What is verified and what is inferred:

• Verified on SQLite: the row counts, the write amplification, the missing ORDER BY/LIMIT, and the one-parameter-per-key IN, including a 2,200-parameter command.
• Inferred from code and documented server limits (not run): the SQL Server failure (The incoming request has too many parameters. The server supports a maximum of 2100 parameters.) and the stalled outbox that follows. The same goes for the PostgreSQL and SQLite thresholds.

How EF Core compares

The EF KeyValueStore.FindManyAsync calls store.QueryAsync(filter.Apply, …). That means:

• ORDER BY Id plus TOP/LIMIT batchSize, so the item lookup is bounded at batchSize keys;
• Keys.Contains(Id) is sent as a single JSON parameter (OPENJSON) on EF Core 9. On EF Core 10 it is sent as padded scalar parameters, with an automatic fallback to OPENJSON above the SQL Server limit.

So EF neither reads the whole outbox nor hits the 2,100-parameter limit. MongoDb also uses filter.Apply.

Suggested fix

In DapperKeyValueStore.ApplyFilter:

• Take == 0: return no rows without querying.
• OrderByKey: append order by Id.
• Take set: append the dialect's limit via Page(PageArgs.FromRange(0, Take)). SQL Server's OFFSET/FETCH requires an ORDER BY, so order by Id whenever Take is set.

As a guard against large explicit key lists (for example a configured batch size above about 2,000), split Keys into chunks of about 1,000 and merge the results.

Tests to add (Elsa.Persistence.Dapper.UnitTests, and the #265 suite)

• On a fresh migrated SQLite DB:
  • FindManyAsync(StartsWith, OrderByKey, Take = 3) returns exactly 3 rows, in ascending key order.
  • Take = 0 returns no rows.
  • Take without OrderByKey returns at most Take rows.
• Outbox poll: with 250 pending items, FindManyAsync(100) reads at most about 301 rows and performs no writes, and the item lookup has at most 100 parameters.
• Run the same assertions on PostgreSQL and SQL Server in the conformance suite. On SQL Server, add a backlog of more than 2,100 pending items: FindManyAsync(100) must succeed and return the 100 oldest items.
• SQL-shape test: the generated SQL contains order by Id and the dialect's limit clause when OrderByKey and Take are set.

Note for core, not Dapper: LegacyKeyPrefix (Elsa:WorkflowDispatchOutbox:) also matches the new Index:, Items: and State: keys. So even with Take honoured, the legacy scan never writes its completion marker while 101 or more outbox rows exist. On Memory/EF it re-reads 101 rows each poll (bounded, no writes).