c# - Yield return database records using LinqToSql? -

i have 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 records in foreach loop. way want save memory usage not caching records recordtable, since linqtosql uses datareader behind scene.

i have 2 questions on scenario though:

1. is true yield-returning above saves resources , works faster caching records array (.toarray())?

2. will data connection automatically closed (i mean using statement) if error occur inside foreach loop of consuming method or if consuming method break foreach loop in middle (like found required record , break)?

in case of executing basic query, can work way (certainly possible) - however, in case of querying naked table<t>, might buffers first; perhaps try querying count during iteration, or running trace. in case suspect buffer first.

re closed: depends ;p if using foreach, yes: since foreach explicitly disposes iterator via finally. however! not guaranteed if does, example (very naughty , lax):

var iter = yourdata.getenumerator(); if(iter.movenext()) {     console.writeline(iter.current.name); // first record of, say, 20 } // , don't dispose iterator == bad 

then since iterator doesn't a: disposed, b: exhaust itself, , c: doesn't crash, won't shut down (any of 3 conditions will close properly). emphasis: pathological case: reasonably safe "it close, yes".

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

ienumerable<customer> customers = connection.query<customer>(        "select * customer", buffered: false); 

(it can handle parameters etc)