return column values as IEnumerable

Question

I have this code working:

public IEnumerable<string> GetEmpNames()
{
    var cmd = SqlCommand("select [EmpName] from [dbo].[Emp]");
    using (var rdr = cmd.ExecuteReader())
        while (rdr.Read())
            yield return (string) rdr["EmpName"];
}

However, I'm wondering if there's a better (LINQish) way, not having to resort to yield return. (And LINQ to SQL is not an option :) )

Answer 1

IEnumerable<string> result = DataContext.ExecuteQuery<string>(sqlstring)

http://msdn.microsoft.com/en-us/library/bb361109.aspx

Answer 2

You should not do that! Your reader needs to be closed as soon as possible. you do not want to keep it open for the duration of the enumeration. It is better to just create an explicit list, and return that.

var cmd = SqlCommand("select [EmpName] from [dbo].[Emp]");
List<string> results = new List<string>();
using (var rdr = cmd.ExecuteReader()) {
    while (rdr.Read())
        results.Add((string) rdr["EmpName"]);
}
return results;

You can use Linq expressions on a DataReader by casting it:

using (var rdr = cmd.ExecuteReader()) {
    results = (from row in rdr.Cast<DbDataRecord>()
               select (string)row["EmpName"]).ToList();
}

But notice that you need to call ToList(), or you will get an error when you try to enumerate because the reader has already been closed.

Edit

There seems to be some confusion in the comments about what a DataReader actually does when it's open. From MSDN:

While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.

Therefore you should close it as soon as possible to free up the connection.