yield return vs. return IEnumerable<T>

Question

I've noticed something curious about reading from an IDataReader within a using statement that I can't comprehend. Though I'm sure the answer is simple.

Why is it that whilst inside the using (SqlDataReader rd) { ... } if I directly perform a yield return the reader stays open for the duration of the read. But if I perform a direct return calling a SqlDataReader extension method (outlined below) that the reader closes before the enumerable can be actualized?

public static IEnumerable<T> Enumerate<T>(this SqlDataReader rd)
{
    while (rd.Read())
        yield return rd.ConvertTo<T>(); //extension method wrapping FastMember

    rd.NextResult();
}

To be absolutely clear of what I'm asking, I'm unsure why the following are fundamentally different:

A fleshed out example, as per @TimSchmelter's request:

/*
 * contrived methods
 */
public IEnumerable<T> ReadSomeProc<T>() {
    using (var db = new SqlConnection("connection string"))
    {
        var cmd = new SqlCommand("dbo.someProc", db);

        using(var rd = cmd.ExecuteReader())
        {
            while(rd.Read())
                yield return rd.ConvertTo<T>(); //extension method wrapping FastMember
        }
    }
}


//vs
public IEnumerable<T> ReadSomeProcExt<T>() {
    using (var db = new SqlConnection("connection string"))
    {
        var cmd = new SqlCommand("dbo.someProc", db);

        using(var rd = cmd.ExecuteReader())
        {
            return rd.Enumerate<T>(); //outlined above
        }
    }
}

/*
 * usage
 */
var lst = ReadSomeProc<SomeObect>();

foreach(var l in lst){
    //this works
}

//vs
var lst2 = ReadSomeProcExt<SomeObect>();

foreach(var l in list){
    //throws exception, invalid attempt to read when reader is closed
}

Answer 1

Summary: Both versions of the method defer, but because ReadSomeProcExt doesn't defer execution, the reader is disposed before execution is passed back to the caller (i.e. before Enumerate<T> can run). ReadSomeProc, on the other hand, doesn't create the reader until it's been passed back to the caller, so it doesn't dispose the container until all its values have been read.

When your method uses yield return, the compiler actually changes the compiled code to return an IEnumerable<>, and the code in your method will not run until other code starts iterating over the returned IEnumerable<>.

That means that the code below doesn't even run the first line of your Enumerate method before it disposes the reader and returns a value. By the time someone else starts iterating over your returned IEnumerable<>, the reader has already been disposed.

using(SqlDataReader rd = cmd.ExecuteReader()){
    return rd.Enumerate<T>();
}

But this code would execute the entire Enumerate() method in order to produce a List<> of results prior to returning:

using(SqlDataReader rd = cmd.ExecuteReader()){
    return rd.Enumerate<T>().ToList();
}

On the other hand, whoever's calling the method with this code doesn't actually execute the method until the result is evaluated:

using(SqlDataReader rd = cmd.ExecuteReader()){
    while(rd.Read())
        yield return rd.ConvertTo<T>(); //extension method wrapping FastMember
}

But the moment they execute the returned IEnumerable<>, the using block opens up, and it doesn't Dispose() until the IEnumerable<> finishes its iterations, at which point you will have already read everything you need from the data reader.

Answer 2

It's because the "yield return" will return one element and continue the iteration, while the "normal" return will finish the invocation.