Using IDataReader to read the values correctly

Question

I use the datareader to retrieve some values from sqltable using sqlcommand. Now when I execute the reader for first time to select the values and assign them to RemovedRows variable, it works perfectly. But when it executes next time to populate AddedRows variable, it returns nothing. Now if I swap the variable positions, AddedRows will have values and the later won't have anything. So basically reader is executing once and next time it won't execute and also doen't give any error like reader is closed etc.

using (IDataReader reader = _changeTable.ExecuteReader())
{
   RemovedRows = reader.Select(r => new { LogID = r["LogID"], Operation = r["SYS_CHANGE_OPERATION"] })
                       .Where(r => r.Operation.Equals("D")).Select(r => (long)r.LogID).ToList();

   AddedRows = reader.Select(r => new { LogID = r["LogID"], Operation = r["SYS_CHANGE_OPERATION"] })
                     .Where(r => r.Operation.Equals("I")).Select(r => (long)r.LogID).ToList();
}

public static IEnumerable<T> Select<T>(this IDataReader reader,
                                           Func<IDataReader, T> projection)
{
    while (reader.Read())
    {
        yield return projection(reader);
    }
}

One solution is having the values of reader in new List<T> and access the new list values without worrying about reader state. I am able to create a List but as the properties are of anonymous type I cannot access it.

I create the new List<T> and use it like this:

    public static List<T> CreateList<T>(params T[] elements)
    {
       return new List<T>(elements);
    }


 var values = reader.Select(r => new { LogID = r["LogID"], Operation = r["SYS_CHANGE_OPERATION"] }).ToList();
 var newList = Helper.CreateList(values);

 RemovedRows = newList.Where(r => r.Operation.Equals("D")).Select(r => (long)r.LogID).ToList();

Here it doesn't allow me to access Operation and LogID properties of the anonymous type.

So my question is how can I execute the reader (code below) and use its values later after closing the reader:

var values = reader.Select(r => new { LogID = r["LogID"], Operation = r["SYS_CHANGE_OPERATION"] }).ToList();

Answer 1

The reader has already been enumerated, and only allows one-time forward-only access, so trying to enumerate it again to get AddedRows results in an empty collection. I'd suggest reading all relevant rows into memory, then filtering into separate queries:

string[] ops = {"D","I"};

IEnumerable<long> removedRows;
IEnumerable<long> addedRows;

using (IDataReader reader = _changeTable.ExecuteReader())
{
    var allRows = reader.Select(r => new { LogID = r["LogID"], Operation = r["SYS_CHANGE_OPERATION"] })
                        .Where(r => ops.Contains(r.Operation)
                        .ToList();

    removedRows = allRows.Where(r => r.Operation.Equals("D"))
                         .Select(r => (long)r.LogID);

    addedRows = allRows.Where(r => r.Operation.Equals("I"))
                       .Select(r => (long)r.LogID);
}

You could also use GroupBy or ToLookup to get a similar result, but use whatever makes the most sense to you unless you have significant performance issues.