Consuming a DataReader with LINQ

Last night at the Atlanta MS Pros meeting, I gave the first my new talks on LINQ Migration Strategies. If you missed it, you can catch me at the Raleigh, NC and Huntsville, AL code camps. Baring that, check out the presentation slides. This talk focuses on how you can extend existing structures using the power of LINQ. Among other things, this includes LINQ to DataSets and the XML Bridge classes.

During the presentation, fellow MVP, Keith Rome asked a question that I couldn't let sit. Is it possible to us LINQ with a data reader? I answered that it should be possible if you combine the power of C# (sorry, VB doesn't have this yet) iterators with the concept of using the fields collection in Datasets. Essentially an untyped dataset is an array of arrays. The first array is consumed by LINQ through the iterator.

The challenge here is that LINQ works on anything that implements IEnumerable, but the DataReader doesn't implement that; at least not natively. Here's where the fun of Extension Methods comes to play. With a C# extension method, we can expose an IEnumerable pattern as we iterate over the rows that we read.

To create an extension method in C#, we create a static method in a static class. We then decorate the first parameter of the method with the "this" keyword to indicate that we are extending that type. In this sample, I wanted to expose the results as an IEnumerable<IDataRecord>, but I couldn't figure out how to get a handle on the current record to yield it as we are iterating. I did find that you can push a row's data into a object collection, so that's what I did in this example. I welcome other recommendations to keep things more strongly typed. Here's the extension method implementation.

public static class DataReaderExtension
{
    public static IEnumerable<Object[]> DataRecord(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;
        }
    }
}

With this extension method, we can now create a data reader and query it using LINQ to Objects as follows:

Using cn As New System.Data.SqlClient.SqlConnection(MyConnectionString)
    cn.Open()
    Using cm = cn.CreateCommand
        cm.CommandType = Data.CommandType.Text
        cm.CommandText = "Select IsApproved, EnteredDate, Creator from Comments"
        Using dr = cm.ExecuteReader
            Me.Listbox1.DataSource = _
                From row In dr.DataRecord _
                Where CBool(row(0)) _
                Order By CDate(row(1)) _
                Select CStr(row(2)) Distinct
            Listbox1.DataBind()
        End Using
    End Using
End Using

I am not happy about the excessive casting to and from object in this implementation. As a result of the extra casting, I suspect that it doesn't perform as well as more native implementations even though we are consuming a data reader, but I haven't had the chance to actually run performance comparisons on the alternatives. Alternative solutions are welcome.