[Order 07][P2] Review synchronous query resource lifetime for unenumerated results

[AGiorgetti]

Priority order

Order: 07 of 09
Priority: P2 correctness/operability

Problem

Synchronous query APIs open database resources before returning lazy enumerables.

Evidence:

• src/NEventStore.Persistence.Sql/SqlPersistenceEngine.cs:460
• ExecuteQuery opens connection/transaction/statement before returning the lazy enumerable.
• Disposal depends on the returned enumerable being enumerated and disposed.

Impact

• Callers that create but do not enumerate a result can leak an open connection/command.
• Common LINQ terminal operations dispose correctly, but non-enumerated results are easy to miss.

Mandatory test-first workflow

• Add tests first that reproduce or verify resource lifetime behavior.
• Include a fake connection/statement test proving whether resources open before enumeration.
• Include a test proving disposal on early termination.
• Do not change resource-lifetime behavior until the intended contract is encoded in tests.

Proposed action

• Consider making sync query methods iterator blocks that open resources on enumeration.
• Alternatively document the disposal contract and add coverage for common paths if changing behavior is too risky.

Acceptance criteria

• Resource lifetime behavior is explicit and tested.
• Early enumeration termination disposes resources.
• The selected contract is documented if lazy enumerable behavior remains.

Source: docs/Project-Analysis-Issue-Drafts.md.