"No data exists for the row/column" exception after using ToList

Question

I have an extension method to convert a DbDataReader to an IEnumerable object:

public static IEnumerable<IDataRecord> AsEnumerable(this DbDataReader reader) {
    while (reader.Read()) {
        yield return reader;
    }
}

In my application, I query the database like so:

var records = context.Query("select WRKORDNBR from WRKORDER").AsEnumerable();

And later, I enumerate each of the records and grab the value of the WRKORDNBR field:

foreach (var record in records) {
    DoSomething((int)record["WRKORDNBR"]);
}

This worked fine before. Now, the reason I bothered converting the DbDataReader is because I need to perform a LINQ query on the records before the foreach block. As you know, the records in DbDataReader can only be enumerated once (or am I totally missing something)? This means that I have to copy the results of AsEnumerable to a List so that I can enumerate them multiple times.

var temp = records.ToList();

At least, I thought that's what I had to do. But when I change my foreach loop to iterate over the temp collection, I get:

InvalidOperationException: No data exists for the row/column

What am I missing here?

Answer 1

Your enumerable does not contain distinct objects, but all enumerations return the same instance (your reader). You are basically doing the following:

var list = new List<IDataRecord>();
while (reader.Read()) 
{
    list.Add(reader);
}

Once the loop finishes, reader contains nothing, which is what you get if you look at any of the "items" in your list. If you try stopping at the penultimate item, you'd also see they all contain the same values.

The reason it works without the list is because you are doing something along the line of:

while (reader.Read()) 
{
    DoSomething((int)record["WRKORDNBR"]);

}

Answer 2

If you have a reader, but you want a dataset, load the dataset...

If you have a dataset, but you want a queryable collection, use the dataset extensions.