Entity Framework - Leaving DB Context Open vs Continually re-creating

Question

I have a fairly simple Winforms / Entity Framework (v6) program that:

1. Queries a Database to populate form elements
2. Upon user click, re-queries the Database to get the relevant information
3. Performs calculations on that information and displays it to the user

Being an EF newbie, I've tried to follow examples of things I've found online and have come up with something fairly simple for the populating / querying along the lines of:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using ctx As New MyEntities
            <Query DB to populate initial values for first combobox>
    End Using
End Sub

Private Sub cboVal1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboVal1.SelectedIndexChanged
    Using ctx As New MyEntities
         <Queries to populate the other controls based upon user selections>
     End Using
End Sub

Private Sub Button_Press(sender As Object, e As EventArgs) Handles MyButton.Click
     Using ctx As New MyEntities
         <Queries to get data, based upon user selections for calculations>
     End Using
End Sub

---

What I'm discovering is that the part that seems to be slowing down my program (and please correct me if I'm wrong about this - As I said, I am a newbie) it that I'm re-establishing a new DB connection each time I use the:

Using ctx As New MyEntities
    ...
End Using

in my code.

So, what I am thinking about doing is to have a form-level variable ctx as MyEntities - To establish the connection on form-load and close the connection on form closing and to keep using the same one throughout... something along the lines of:

Dim ctx as MyEntities

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ctx = New MyEntities

    <Query ctx to populate initial values for first combobox>
End Sub

Private Sub cboVal1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboVal1.SelectedIndexChanged
    <Queries ctx to populate the other controls based upon user selections>
End Sub

Private Sub Button_Press(sender As Object, e As EventArgs) Handles MyButton.Click
     <Queries ctx to get data, based upon user selections for calculations>
End Sub

Private Sub Main_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
    ctx.Dispose()
    ctx = Nothing
End Sub

When I switched things to work this way, it seems to have increased speed dramatically, I'm aware that it leaves me open to the possibility of possibly making bad changes to the DB, but this is a small project that doesn't do any updating - just querying... Is this a reasonable solution or am is this a dangerous way to do things?

Answer 1

Database connections are generally going to be pooled in a connection pool with just about any modern querying tool. This will look much like the thread pool does; some number of connections will be opened and whenever a new context is created and requests a connection it will be given exclusive use of one of the existing connections. When the context is disposed the connection won't be closed, it'll just be returned to the connection pool.

Because of this there is no need to try to manually keep contexts alive for long periods of time. Use them for just a single operation.

Of course it's possible to disable connection pooling if you explicitly don't want it to happen, but there is pretty rarely cause for doing that.