Return sql query results with deferred execution

Question

I have a method to execute an SQL statement and it returns an instance of my class ResultSet that contains the rows and columns returned. I only want to read the data row-by-row when the caller actually iterates over the results.

public class ResultSet {
    public IEnumerable<Row> Rows {
        get;
        private set;
    }
    public ResultSet(IEnumerable<Row> rows, IEnumerable<Column> columns) {
        Rows = rows;
        // columns and other code omitted
    }
}

For this, I tried passing an IEnumerable to the constructor of my class, but the problem is that the DbDataReader instance has already been disposed by the time you try to iterate over the Rows property of a ResultSet instance:

    public ResultSet Execute(string sql) {
        using (var command = Connection.CreateCommand()) {
            command.CommandText = sql;

            var reader = command.ExecuteReader();
            try {
                IEnumerable<Row> MyFunc()
                {
                    while (reader.Read())
                        yield return new Row(reader);
                }
                var columns = GetColums(...);
                return new ResultSet(MyFunc(), columns);
            } finally {
                reader.Dispose();
            }
        }
    }

I know I could pass the DbDataReader instance to my class and not dispose it in the Execute method but then I would have to make ResultSet disposable and I would like to avoid that, if possible. I'm not sure it's even possible what I'm trying to do?

I have looked at yield return statement inside a using() { } block Disposes before executing but it's not quite the same as it doesn't involve a disposable resources.

Create a custom IEnumerable that calls ExecuteReader when GetEnumerator is called, and no earlier. (Iterator methods can return IEnumerator as well, so this isn't as troublesome as it sounds.) Or, always an option, give this kind of manual ORM code a pass and use Dapper, which tends to fit these sorts of scenarios quite well. — Jeroen Mostert
Or create a custom IEnumerable<Row> building custom IEnumerator<Row> that execute the query the first time MoveNext is called. — vc 74
The reader already reads data row by row (unless it's a small set of records in which case, it will read them all in one shot, internally) so why do you need to use yield? (disclaimer: I dislike yield). — JuanR
Either you defer the whole request (both rows and columns) or you will need 2 requests (one to eagerly load columns, one to lazily load rows). What do you prefer ? — Spotted
@Spotted You're right, that makes sense now that I think about it. I think I will just opt to return the DbDataReader instance and have an extension methods for GetRows and GetColumns on that. — user3700562

vc 74 vc 74 · Accepted Answer · 2018-07-23T14:47:40

To illustrate my comment:

public class Row
{
    public Row(IDataReader reader)
    { }
};

public class RowEnumerator : IEnumerator<Row>
{
    public RowEnumerator(IDbConnection connection, string SQL)
    {
        _command = connection.CreateCommand();
        _command.CommandText = SQL;

        _reader = _command.ExecuteReader();
    }
    private readonly IDbCommand _command;
    private readonly IDataReader _reader;

    public Row Current => new Row(_reader);

    object IEnumerator.Current => Current;

    public bool MoveNext() => _reader.Read();

    public void Reset() => throw new NotImplementedException();

    public void Dispose()
    {
        _reader.Dispose();
        _command.Dispose();
    }
}

public class RowEnumerable : IEnumerable<Row>
{
    public RowEnumerable(IDbConnection connection, string SQL)
    {
        _connection = connection;
        _SQL = SQL;
    }
    private readonly IDbConnection _connection;
    private readonly string _SQL;

    public IEnumerator<Row> GetEnumerator() => new RowEnumerator(_connection, _SQL);

    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();
}

The query is executed when a RowEnumerator is created, i.e. when RowEnumerable.GetEnumerator is called.

Is the enumerable is used in a foreach loop, the enumerator, and therefore the command and reader, will be disposed automatically. Otherwise you have to dispose it manually.

Return sql query results with deferred execution

1 Answers