IDataReader is closed when I try to return a tuple

Question

I'm trying to return an HTTP Response that consists of an array of records I've selected from a database. But I'm having trouble mapping my IDataReader to an Enumerable. Here's the part of my code that's relevant:

namespace Web.Api.Controllers
{
    public static class Blah
    {
        public static IEnumerable<T> Select<T>(this IDataReader reader,
                                       Func<IDataReader, T> projection)
        {
            while (reader.Read())
            {
                yield return projection(reader);
            }
        }
    }
    
    [HttpPost]
    [Route("Search")]
    public async Task<Tuple<int, IEnumerable<CustomerViewModel>, int>> Search([FromBody]CustomerViewModel model)
    {
        var s = _configuration.GetConnectionString("DefaultConnectionAlt");
        

        using (SqlConnection connection = new SqlConnection(s))
        {

            connection.Open();

            using (SqlCommand command = new SqlCommand("Search @RegistrationDate=\"2020-07-09\"", connection))
            {
                using (IDataReader reader = command.ExecuteReader())
                {
                    var results = reader.Select<CustomerViewModel>(CustomerViewModel.Create);
                    return Tuple.Create(0, results, 29);
                }
            }
        }

    }
}

When I send a POST request to http://localhost:42432/api/Search, the line while (reader.Read()) gives the error:

System.InvalidOperationException: 'Invalid attempt to call Read when reader is closed.'

I put a breakpoint at return Tuple.Create(0, results, 29);, and when I inspect the results variable, it shows the results I expect. But after I step out of that breakpoint, I get the error on the while (reader.Read()).

Can someone tell me how I can fix my problem?

---

I was following examples listed here:

How can I easily convert DataReader to List<T>?

Convert rows from a data reader into typed results

---

EDIT - I am using dotnetcore

Answer 1

What you're seeing is the effect of deferred execution when using IEnumerable, in this case, the IEnumerable<T> that's being returned from your Select method.

You're returning an IEnumerable<CustomerViewModel> as part of the tuple, which at this point hasn't been executed. Then the connection & reader are being disposed through the usage of using.

When you subsequently try to iterate the IEnumerable<CustomerViewModel> after the method has returned, the captured data reader has already been closed as part of the dispose.

When you inspect through the debugger, Results is a method that iterates over the enumerable, and at this point the data reader has not yet been disposed.

One option to prevent this would be to call ToList on your results before you return.

---

This is a similar concept to generators in javascript.

Answer 2

Remember, that IEnumerable are processed lazily, on demand. In your particular case, Search returns tuple value with the IEnumerable that is not yet read. Therefore reader is being closed before the IEnumerable is populated.