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

Please clarify the version of the skd you are using (,net framework or dot net core)? — Nicklaus Brain, Jul 11 '20 at 10:54
@NicklausBrain I edited my question to say I'm using dot net core — John, Jul 11 '20 at 11:00
Does this answer your question? [What are the benefits of a Deferred Execution in LINQ?](https://stackoverflow.com/questions/7324033/what-are-the-benefits-of-a-deferred-execution-in-linq) — GSerg, Jul 11 '20 at 11:02

Oliver · Accepted Answer · 2020-07-11T11:13:40.947

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.

score 1 · Answer 2 · answered Jul 11 '20 at 11:02

1

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.

answered Jul 11 '20 at 11:02

Nicklaus Brain

884
6
15

IDataReader is closed when I try to return a tuple

2 Answers2