Can I create an anonymous type collection from a DataReader?

Question

I have this code:

var query = "SELECT * FROM Cats";

var conn = new SqlConnection(sqlConnectionString);

conn.Open();

var cmd = new SqlCommand(query);
var reader = cmd.ExecuteReader();

while (reader.Read())
{
    var CatName = reader.GetString(0);
    var CatDOB = reader.GetDateTime(1);
    var CatStatus = reader.GetInt32(2);
}

I'd like to pull the rows out into an anonymous type collection, which I'd normally do using LINQ to iterate, but I an not sure if it's possible due to the way you have to call .Read() each time to get the next row.

Is there a way to do this?

score 8 · Accepted Answer · answered Nov 26 '13 at 17:16

8

You can create helper generic method and let compiler infer type parameter:

private IEnumerable<T> Select<T>(DbDataReader reader, Func<DbDataReader, T> selector)
{
    while(reader.Read())
    {
        yield return selector(reader);
    }
}

usage:

var items = SelectFromReader(reader, r => new { CatName = r.GetString(0), CarDOB = r.GetDateTime(1), CatStatus = r.GetInt32(2) });

You can even make the method an extension method on DbDataReader:

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

and use it like that:

var items = reader.Select(r => new { CatName = r.GetString(0), CarDOB = r.GetDateTime(1), CatStatus = r.GetInt32(2) });

answered Nov 26 '13 at 17:16

MarcinJuraszek

124,003
15
196
263

2

`DbDataReader` implements `IEnumerable`, so you don't actually need to define your own `Select`, you could just use `Cast` and then the LINQ select, if this was your goal. – Servy Nov 26 '13 at 17:24
1

For the record, the `DbDataReader` needs to be cast to `IDataRecord`: `var values = dataReader.Cast().Select(r => new { PropertyOne = r["ValueOne"].TryCastOrParse(), // etc. });` – Mark Avenius Jun 05 '15 at 17:03
@MarkAvenius that's a great tip about SqlDataReader.Cast(). thanks! – symbiont Oct 10 '19 at 01:19

Hogan · Answer 2 · 2013-11-26T17:25:18.017

5

Here is an example of doing it with dynamic (which I think is easier to work with) but some may feel does not adhere to the letter of your question.

Call it like this:

var result = SelectIntoList("SELECT * FROM Cats",sqlconnectionString);

You could (like I did) put it into a static class in a separate file for easier maintanence.

public static IEnumerable<dynamic> SelectIntoList(string SQLselect, string connectionString, CommandType cType = CommandType.Text)
{
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
    using (SqlCommand cmd = conn.CreateCommand())
    {
      cmd.CommandType = cType;
      cmd.CommandText = SQLselect;

      conn.Open();

      using (SqlDataReader reader = cmd.ExecuteReader())
      {

        if (reader.Read())  // read the first one to get the columns collection
        {
          var cols = reader.GetSchemaTable()
                       .Rows
                       .OfType<DataRow>()
                       .Select(r => r["ColumnName"]);

          do
          {
            dynamic t = new System.Dynamic.ExpandoObject();

            foreach (string col in cols)
            {
              ((IDictionary<System.String, System.Object>)t)[col] = reader[col];
            }

            yield return t;
          } while (reader.Read());
        }
      }

      conn.Close();
    }
  }
}

edited Nov 26 '13 at 17:25

answered Nov 26 '13 at 17:17

Hogan

69,564
10
76
117

`dynamic`? `ExpandoObject`? I can tell you, it will make a lot of runtime checkes and it's not gonna be fast. – MarcinJuraszek Nov 26 '13 at 17:19
This isn't a collection of anonymous types. `ExpandoObject` is a named type. (Granted, a special one, but still not an anonymous one.) – Servy Nov 26 '13 at 17:23
@MarcinJuraszek - Don't be to sure, performance has been fine for me, this is basically what SO is built on, see https://github.com/SamSaffron/dapper-dot-net, the time for such checks is relatively small compared with any DB access. – Hogan Nov 26 '13 at 17:24
Much more than performance, (and also technically not answering the question) I'd personally be much more concerned with the loss of compile time type checking. This is much harder to work with, as a developer, as you can easily mis-type a column name when using the sequence, or not remember what all of the types are, etc. – Servy Nov 26 '13 at 17:26
@Servy - I have another version where I pass in an object to match against the read object for when I want type checking, I mostly use this for quick tests, proto-typing, POCs and one off conversion code when I need something quick. But it does work and I believe answers the question's intent. I edited the answer to point out how it is different for you and MarcinJuraszek. – Hogan Nov 26 '13 at 17:30

score 3 · Answer 3 · answered Nov 26 '13 at 17:18

It's possible, although not particularly neat. We'll need to create a new method that will allow us to create an empty sequence that allows for type inference off of a dummy value for starters:

public static IEnumerable<T> Empty<T>(T dummyValue)
{
    return Enumerable.Empty<T>();
}

This lets us create a list of an anonymous type:

var list = Empty(new
{
    CatName = "",
    CatDOB = DateTime.Today,
    CatStatus = 0
}).ToList();

(The item here isn't used.)

Now we can add our anonymous types to this list:

var cmd = new SqlCommand(query);
var reader = cmd.ExecuteReader();

while (reader.Read())
{
    list.Add(new
    {
        CatName = reader.GetString(0),
        CatDOB = reader.GetDateTime(1),
        CatStatus = reader.GetInt32(2),
    });
}

Of course, using a named type would likely be easier, so I would suggest using one unless there is a real compelling reason not to do so. That is especially true if you plan to use the list outside of the scope it's created in.

Thanks, perhaps a named type or a Tuplet is the way to go for tidiness. I appreciate the answers though, this is clever stuff! I will certainly make a note :) — NibblyPig, Nov 26 '13 at 17:21
@SLC I assume you mean `Tuple`, not `Tuplet`. And as I said in the answer, a named type is likely going to be preferable. — Servy, Nov 26 '13 at 17:21

score 0 · Answer 4 · answered Dec 22 '17 at 00:09

Technically, it may not answer your question, but simply don't use a reader. Instead use a SqlDataAdapter to Fill a DataSet, if you can. Take the 0th Table of that DataSet, and select a new anonymous object from the Rows collection.

using System.Data; // and project must reference System.Data.DataSetExtensions

var ds = new DataSet();
using (var conn = DbContext.Database.GetDbConnection())
using (var cmd = conn.CreateCommand())
{
  cmd.CommandType = CommandType.Text;
  cmd.CommandText = sqlText;
  conn.Open();
  (new SqlDataAdapter(cmd)).Fill(ds);
}
var rows = ds.Tables[0].AsEnumerable(); // AsEnumerable() is the extension
var anons = rows
  .Select(r => new { Val = r["Val"] })
  .ToList();

Can I create an anonymous type collection from a DataReader?

4 Answers4

Linked