Yield return database records using LinqToSql?

Question

I have a supplying method in DAL:

public IEnumerable<RecordType> GetRecords()
{
    using (LinqDataContext context = new LinqDataContext())
    {
        var records = context.RecordTable;

        foreach (RecordType record in records)
        {
            yield return record;
        }
    }
}

The consumer method uses the records in a foreach loop. This way I want to save a memory usage by not caching all the records from RecordTable, since LinqToSql uses a DataReader behind the scene.

I have two questions on this scenario though:

1. Is that true that yield-returning above saves resources and works faster than caching all records to an array (.ToArray())?

2. Will the data connection be automatically properly closed (I mean the using statement) if an error will occur inside the foreach loop of the consuming method OR if the consuming method will break the foreach loop in the middle (like found a required record and break)?

Answer 1

In the case of executing a basic query, it can work that way (certainly it is possible) - however, in the case of querying a naked Table<T>, it might be that it all buffers first; you could perhaps try querying the count during the iteration, or running a trace. In this case I suspect it will buffer first.

Re closed: that also depends ;p If someone is using foreach, then yes: since foreach explicitly disposes the iterator via finally. However! It is not guaranteed if someone does, for example (very naughty and lax):

var iter = yourData.GetEnumerator();
if(iter.MoveNext()) {
    Console.WriteLine(iter.Current.Name); // first record of, say, 20
}
// and don't dispose the iterator == bad

then since the iterator doesn't a: get disposed, b: exhaust itself, and c: doesn't crash, it won't shut down properly (any of those 3 conditions will close it properly). Emphasis: this is a pathological case: normally it is reasonably safe to say "it will close, yes".

If you want guaranteed non-buffering, note that "dapper" has that, if you set buffered to false:

IEnumerable<Customer> customers = connection.Query<Customer>(
       "select * from Customer", buffered: false);

(it can also handle the parameters etc)