Casting Ado.net DataReader to IDataRecord giving strange result

Question

I have a query that I run against the database, and I can see that there is a record for 31/05/2013. When I run this query from C# with ADO.NET, and then use the following code, I am missing the record for 31/05/2013

var timeSeriesList = new List<TimeSeries>();  
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        timeSeriesList = reader.Cast<IDataRecord>()
            .Select(r => new TimeSeries
                 {
                     MidRate = (double)r["MidRate"],
                     RiskFactorName = (string)r["RiskFactorName"],
                     SeriesDate = (DateTime)r["SeriesDate"]
                 }).ToList();

    }
}

However, if I use the same query with this code:

var timeSeriesList = new List<TimeSeries>();                        
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        var timeSeries = new TimeSeries
                 {
                     MidRate = (double)reader["MidRate"],
                     RiskFactorName = (string)reader["RiskFactorName"],
                     SeriesDate = (DateTime)reader["SeriesDate"]
                 };

        timeSeriesList.Add(timeSeries);
    }
}

...then the record at 31/05/2013 is in the collection - why would the first block of code give this result?

Answer 1

I think that you are missing record in first example because you move reader by one and then cast it.

Try this change and see if it worked:

var timeSeries = new List<TimeSeries>();  
using (var reader = cmd.ExecuteReader())
{
    if (reader.HasRows)
    {
        timeSeries = reader.Cast<IDataRecord>()
            .Select(r => new TimeSeries
                 {
                     MidRate = (double)r["MidRate"],
                     RiskFactorName = (string)r["RiskFactorName"],
                     SeriesDate = (DateTime)r["SeriesDate"]
                 }).ToList();
    }
}

Answer 2

There are two ways of iterating through a data-reader; one is to keep calling .Read(); the other is to treat it as an IEnumerable sequence of IDataRecord and foreach it; no matter which you choose you can only iterate the data once.

• the call to .Read() moves from the BOF to the first record, if one
  • the ToList() calls GetEnumerator() then MoveNext() in a loop, which immediately moves forward one position (so we've dropped the first record on the floor without processing it); at the end of the ToList() we have chewed through all the data
• so the outer .Read() will then report false (EOF)

Basically: the problem here is using two APIs that progress the position. Either use .Read(), or use the foreach API (.ToList()).

As a side note, since the column names match the member names you could also use "dapper" to do the heavy lifting:

var list = conn.Query<TimeSeries>(sql, args).ToList();