Entity Framework - DB output Open context vs Permanent re-creation

Question

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

• Database queries to fill out form elements
• After clicking, the user re-queries the database to obtain the relevant information.
• Performs calculations on this information and displays it to the user.

As a newcomer to EF, I tried to follow examples of things I found on the Internet, and came up with something pretty simple to populate / query line by line:

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 discover is that the part that seems to slow down my program (and please correct me if I'm wrong about this . As I said, I'm new) that I restore a new connection to the database every time, when i use:

 Using ctx As New MyEntities ... End Using 

in my code.

So, I think that you need to have a ctx as MyEntities form level variable - establish a connection to the upload form and close the connection when you close the form and continue to use the same throughout ... something along the lines:

 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 everything to work in this way, it seemed to dramatically increase the speed, I know that this leaves me the opportunity to make bad changes to the database, but this is a small project, t to do any kind of update is just a request ... Is this a smart decision, or is it a dangerous way to do something?

Answer 1

Database connections are usually collected in a connection pool with almost any modern query tool. It will be very similar to a thread pool; a certain number of connections will be opened, and whenever a new context is created and a connection is requested, it will be granted exclusive use of one of the existing connections. When the context is located, the connection will not be closed, it will simply be returned to the connection pool.

Because of this, there is no need to try to manually keep contexts alive for extended periods of time. Use them for only one operation.

Of course, you can disable the connection pool if you clearly do not want this to happen, but this is rarely the cause.