How can I easily convert DataReader to List<T>?

Question

I have data in a DataReader which I want to be converted to a List<T>. What is a possible simple solution for this?

For e.g. in CustomerEntity class, I have CustomerId and CustomerName properties.If my DataReader returns these two columns as data, then how can I convert it into List<CustomerEntity>.

Answer 1

I would suggest writing an extension method for this:

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

You can then use LINQ's ToList() method to convert that into a List<T> if you want, like this:

using (IDataReader reader = ...)
{
    List<Customer> customers = reader.Select(r => new Customer {
        CustomerId = r["id"] is DBNull ? null : r["id"].ToString(),
        CustomerName = r["name"] is DBNull ? null : r["name"].ToString() 
    }).ToList();
}

I would actually suggest putting a FromDataReader method in Customer (or somewhere else):

public static Customer FromDataReader(IDataReader reader) { ... }

That would leave:

using (IDataReader reader = ...)
{
    List<Customer> customers = reader.Select<Customer>(Customer.FromDataReader)
                                     .ToList();
}

(I don't think type inference would work in this case, but I could be wrong...)