Convert SqlDataReader to linq expression

Question

When looking at examples on the internet on how to use SqlDataReader I found things like:

var command = new SqlCommand(  // initialize with query and connection...
var reader = command.ExecuteReader();

while(reader.Read())
{
    var foo = reader["SomeColName"];
    // etc
}

Can I use the following extension method:

public static IEnumerable<IDataReader> ToEnumerable(this IDataReader reader)
{
     while (reader.Read())            
         yield return reader;            
}  

In order to execute queries using linq?

If I use the following extension method will it be bad? If I care about performance should I use the first implementation?

Solution 1

I can't see how your proposed extension would help you, as you only get an enumeration containing the same instance of IDataReader for each read row.

I'm not sure if there is any good way to combine IDataReader with LINQ, but you may want something like that:

public static IEnumerable<Dictionary<string, object>> ToEnumerable(this IDataReader reader)
{
    while (reader.Read())
    {
        Dictionary<string, object> result = new Dictionary<string, object>();
        for (int column = 0; column < reader.FieldCount; column++)
            result.Add(reader.GetName(column), reader.GetValue(column));
        yield return result;
    }
} 

This will return a sequence of dictionaries (one dictionary per row) which map the column name to the object in that "cell".

Unfortunatly, you lose type safety and create memory overhead. The specific implementation would still depend on what you really want to achieve. Personally, I always use only the column index instead of the name.

---

That said, I think the best approach is still to use the IDataReader directly to create pocos and to use this pocos with LINQ:

IEnumerable<string> GetSomeColValue(IDataReader reader)
{
    while(reader.Read())
        yield return reader.GetString(reader.GetOrdinal("SomeColName"));
}

Of course this is a shortened example. In real code I would read out all data from the reader completely and close/dispose the reader instance inside the method that called ExecuteReader before returning anything.

Solution 2

You just need to cast your IEnumerable to IEnumerable<IDataRecord>

var enumerable = reader.Cast<IDataRecord>();
var col = enumerable.Select(record => record .GetOrdinal("SomeColName"));