When will connection be closed in case of IEnumerable with using

Question

Suppose I have such pseudo code using some pseudo ORM (ok in my case it's 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 Connection be closed in db? Would it be closed at all in that case? What connection would be closed - those in using statement or those in lambda expression? .NET compiler is creating anonymous class for lambdas, so it will copy connection to that class. When would that 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. I materialized queries and exception disappeared. But I'm wondering how this thing works.

While not a duplicate, assuming some generic ORM, it is still pretty much related - http://stackoverflow.com/questions/27510430/why-do-i-need-a-tolist-to-avoid-disposed-context-errors — Eugene Podskal, Dec 26 '16 at 21:40
@EugenePodskal that seems logical to me - dbcontext already being dispose. Strangely, in linq2db that actually works.. — nikita, Dec 26 '16 at 21:42
@juharr No. In SqlProfiler I see request only when calling Any. — nikita, Dec 26 '16 at 21:45
It actually looks like you're just getting a weird error message about timeouts because the connection is disposed. — juharr, Dec 26 '16 at 21:48
`return aItems.Select(…)` will *not* return just a `B` but an `IEnumerable` (unless you left out stuff). So the connection isn’t even opened before the result from `DoSmth` is iterated itself. So to answer your question: From just calling `DoSmth`, the connection will neither be closed nor opened. — poke, Dec 26 '16 at 21:49
@juharr I get this error when DoSmth is called many times. If called not so many times then works fine, Actually I got that error because of some big data on consumer's database. — nikita, Dec 26 '16 at 21:51
@poke true. My fault - misspelled pseudo-code, It actually returns some array of objects. Used for filling some SOAP messages. — nikita, Dec 26 '16 at 21:55
@nikita If DoSmth() get called many times and the return is pretty small maintain a collection in memory and use it — Nithila Shanmugananthan, Dec 27 '16 at 12:18

score 5 · Accepted Answer · answered Dec 27 '16 at 11:42

This is entirely dependent on your ORM. If disposing of ConnectionFactory.Current.GetDBConnection() closes the connection then you will never be able to enumerate the result. If it doesnt close the connection (and something else does) it might work depending on if someone else has closed the connection.

In any case you probably dont want to return an un-enumerated enumerable from something which creates and disposes the connection.

either enumerate the collection before closing it eg:

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

or control the connection at the level which does enumerate the results eg:

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();
  }
}

score 1 · Answer 2 · answered Nov 18 '18 at 16:11

Linq2db release connection when you close your connection object. In your example it happens when you leave using block and db object is disposed. This means that you cannot return query from connection scope - you need to fetch your data first or postpone connection disposal till you didn't get all required data.

Exception you observed was due to error in linq2db prior to 1.8.0 where it allowed query execution on disposed connection, which lead to connection leak. See https://github.com/linq2db/linq2db/issues/445 for more details. After that fix you cannot write code like GetA() in your example as you will get ObjectDisposedException when you will try to enumerate results.

score 0 · Answer 3 · answered Dec 27 '16 at 11:29

0

after first call : in this call connection opened in using scope , without any fetching data at the end of usnig scope Closed.

var aItems = GetA();

but at this line :

if (!aItems.Any())

there isn't any open connection

answered Dec 27 '16 at 11:29

Saman Hajizade

270
1
9

When will connection be closed in case of IEnumerable with using

3 Answers3