When the connection will be closed in case of IEnumerable using

Question

Suppose I have such pseudo-code using some pseudo ORM (ok in my case it is Linq2Db).

static IEnumerable<A> GetA() { using (var db = ConnectionFactory.Current.GetDBConnection()) { return from a in db.A select a; } } static B[] DoSmth() { var aItems = GetA(); if (!aItems.Any()) return null; return aItems.Select(a => new B(a.prop1)).ToArray(); } 

When will the connection be closed in db ? Will it be closed at all in this case? Which connection will be closed - those that are used in the statement or in the lambda expression? The .NET compiler creates an anonymous class for lambdas, so it will copy the connection to this class. When will this connection be closed?

Somehow I managed to get Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. that materialized queries and exception have disappeared. But I wonder how it works.

Answer 1

It totally depends on your ORM. If utilization of ConnectionFactory.Current.GetDBConnection() closes the connection, you can never list the result. If he does not close the connection (and something else), it may work depending on whether someone closed his connection.

In any case, you probably do not want to return an un-enumerated enumerated from what creates and deletes the connection.

either list the collection before it is closed, for example:

 static IEnumerable<A> GetA() { using (var db = ConnectionFactory.Current.GetDBConnection()) { return (from a in db.A select a).ToArray(); } } 

or manage the connection at a level that lists the results, for example:

 static IEnumerable<A> GetA(whatevertype db) { return from a in db.A select a; } static B[] DoSmth() { using (var db = ConnectionFactory.Current.GetDBConnection()) { var aItems = GetA(db); if (!aItems.Any()) return null; return aItems.Select(a => new B(a.prop1)).ToArray(); } } 

Answer 2

after the first call: in this call that opened when using the scope, without any data retrieval at the end of the Closed scope.

 var aItems = GetA(); 

but in this line:

 if (!aItems.Any()) 

no open connection