Is there any problem about create different Database contexts every time without using

Question

Well, I would like to know what is the possible problems about use the following approach

CreateContextFactory().Create().QueryOpenConnectionCount();

instead of:

using (var context = CreateContextFactory().Create())
{
      openConnectionCount = context.QueryOpenConnectionCount();
}
return openConnectionCount;

and also if there is any problem about something like:

CreateContextFactory().Create().QueryOpenConnectionCount1();
CreateContextFactory().Create().QueryOpenConnectionCount2();
CreateContextFactory().Create().QueryOpenConnectionCount3();

This is because I have some methods in a class that are open db contexts as above, I could create a using statement for them, but so I would need to pass the context for all methods, and I would also need to refactor a helper that executes a transaction using a db context (because it creates its own context internally). So what are the problems about let the code stay this way?

Answer 1

Often, a data-context "owns" a connection, to avoid constantly having to fetch one from the pool and initialize it. Disposing the data-context typically disposes that connection.

So: if you keep creating data-contexts without properly disposing them, you're hemorrhaging connections, at least until GC steps in and undoes the mess (releasing the unmanaged part of the connection back into the unmanaged pool, if there is an unmanaged part).

This is a bad thing, and severely limits scalability and increases the number of open connections to your database server. So yes, you should dispose your data-contexts. Extending that to the general case: you dispose any IDisposable thing that you're responsible for, when you're done with it. With perhaps a few exceptions (HttpClient, DataTable, etc).

Note that C# 8 has a slightly terser (I would say "nicer", but that is subjective; I like it a lot) syntax for this, if your using statement reaches to the end of the current scope (i.e. to the next }):

using var context = CreateContextFactory().Create();
return context.QueryOpenConnectionCount();

or even with earlier C# you can make it simpler and remove the local:

using (var context = CreateContextFactory().Create())
{
    return context.QueryOpenConnectionCount();
}