Best method to use IDataReader as IEnumerable?

Question

I need to use Linq on any IDataReader implementations like this

var c = sqlDataReader.AsEnumerable().Count();

Example:

public abstract class Test
{
    public abstract SqlDataReader GetSqlDataReader();

    public void Foo()
    {
        SqlDataReader sqlDataReader = GetSqlDataReader();
        IEnumerable<SqlDataReader> sqlEnumerable = sqlDataReader.AsEnumerable();
        var c = sqlEnumerable.Count();
        var s = sqlEnumerable.Sum();
        SqlDataReader first = sqlEnumerable.First();
        var t = first.GetSqlXml(10);
    }
}

What is the best way to write this. Please, write your snippet.

Answer 1

You can use this:

MyDataReader.Cast<IDataRecord>()

But don't forget to have the linq statement execute before you close the DataReader.
using ToList() for instance

Answer 2

You could create an extension method to do this (see caveats below):

public static class DataReaderExtension
{
    public static IEnumerable<Object[]> AsEnumerable(this System.Data.IDataReader source)
    {
        if (source == null)
            throw new ArgumentNullException("source");

        while (source.Read())
        {
            Object[] row = new Object[source.FieldCount];
            source.GetValues(row);
            yield return row;
        }
    }
}

Found here: http://www.thinqlinq.com/default/Consuming-a-DataReader-with-LINQ.aspx

---

As pointed out by @LukeH, note that as IDataReader only supports reading once, forwards, you'll only be able to query the enumerable once. (To get round this you could call ToList/ToArray, then query that).

Note that SqlDataReader already impliments IEnumerable so you won't need to do this in the example you've given.

Also, be aware that it's probably better to do any filtering/aggrigating on the server (via LINQ to SQL for example)

Answer 3

Try, this:

public static class DataReaderExtension
{
    public class EnumeratorWrapper<T>
    {
        private readonly Func<bool> moveNext;
        private readonly Func<T> current;

        public EnumeratorWrapper(Func<bool> moveNext, Func<T> current)
        {
            this.moveNext = moveNext;
            this.current = current;
        }

        public EnumeratorWrapper<T> GetEnumerator()
        {
            return this;
        }

        public bool MoveNext()
        {
            return moveNext();
        }

        public T Current
        {
            get { return current(); }
        }
    }

    private static IEnumerable<T> BuildEnumerable<T>(
            Func<bool> moveNext, Func<T> current)
    {
        var po = new EnumeratorWrapper<T>(moveNext, current);
        foreach (var s in po)
            yield return s;
    }

    public static IEnumerable<T> AsEnumerable<T>(this T source) where T : IDataReader
    {
        return BuildEnumerable(source.Read, () => source);
    }
}

Answer 4

Here are my two cents :

public static IEnumerable<T> Enumerate<T>(this T reader) where T: IDataReader 
{ 
   using(reader) 
      while(reader.Read()) 
         yield return reader; 
} 

public void Test()
{
  var Res =
    from Dr in MyDataReader.Enumerate()
    select new {
      ID = (Guid)Dr["ID"],
      Description = Dr["Desc"] as string
    };
}

I felt the urge to post this, because it is very important to dispose a DataReader after use, and no answer mentioned it.

That's why my implementation has a using statement around the while loop. In this way, I can do "one hand" queries whithout worrying about the DataReader disposal.

Answer 5

You can just load the DataReader into a DataTable and then Select():

DataTable dt = new DataTable();
dt.Load(dataReader);
DataRow[] rows = dt.Select();        //DataRow[] Implements IEnumerable

or

IEnumerable<DataRow> rows = dt.AsEnumerable();

Answer 6

I've used the following but I like @Serge's suggestion better and it reminds be of what I believe I used to do but then forgot that IDataReader implements

var reader = command.ExecuteReader();
var records = Enumerable
    .Range(0, int.MaxValue)
    .TakeWhile(i => reader.Read())
    .Select(i => reader as IDataRecord);

Answer 7

Today I implemented the following generic solution as we don't like to depend on any third party dependencies or any complex solution.

Simple and Fast IDataReader to IEnumerable<T> conversion using reflection + lambda expressions

public IEnumerable<T> ConvertToEnumerable<T>(SqlDataReader dr) where T : class, new()
{
    List<string> lstColumns = Enumerable.Range(0, dr.FieldCount).Select(dr.GetName).ToList();
    List<PropertyInfo> lstProperties = typeof(T).GetProperties().Where(x => lstColumns.Contains(x.Name, StringComparer.OrdinalIgnoreCase)).ToList();
    while (dr.Read())
    {
        var entity = new T();
        lstProperties.Where(w => dr[w.Name] != System.DBNull.Value).ToList().ForEach(i => i.SetValue(entity, dr[i.Name], null));
        yield return entity;
    }
}

Usages

SqlDataReader dr;
...
var result = ConvertToEnumerable<Foo>(dr).FirstOrDefault();
Or
var result = ConvertToEnumerable<Foo>(dr).ToList();