Proposal: Add BufferResultSets Connection String Option

[caleblloyd]

I'm proposing a new connection string option named BufferResultSets that will read all Result Sets into memory upon calling ExecuteReader and ExecuteReaderAsync. It would default to false and would have to be explicitly enabled.

The intended use case is for ORMs that need to support the SQL Server-like feature Multiple Active Result Sets. MySQL does not support MARS, but by buffering all result sets and clearing the active reader, the connection will be open to service another reader. Since ORMs typically end up reading all results into memory anyways, this should not add much extra memory overhead.

There is a couple ways this could be implemented:

1. Simple: If BufferResultSets is set, immediately buffer all result sets into memory upon calling ExecuteReaderAsync
2. Complex: If a connection receives a call while there is an active reader, first buffer the active reader into memory in order to clear the reader, then service the next call

Logic to buffer result sets already exists in MySqlClient/Results/ResultSet.cs

This option would remove the need for SynchronizedMySqlDataReader.cs in Pomelo.EntityFrameworkCore.MySql.

@bgrainger @kagamine

[bgrainger]

Since ORMs typically end up reading all results into memory anyways, this should not add much extra memory overhead.

Dapper buffers all results by default and claims that it's "ideal in most cases".

[bgrainger]

No objections to this proposal. The "Simple" approach sounds best to me; it doesn't seem like a bad idea to buffer the data immediately if the user has opted-in to it.

[caleblloyd]

I've begun work on the "Simple" approach here: f_bufferResultSets

[bgrainger]

Implemented in 0.15.0.