none
Am I leaking DbContext objects by handling StateChange? RRS feed

  • Question

  • We would like a stored procedure to be called before the Entity Framework makes a call to the database. We're wanting to set the CONTEXT_INFO on the connection to help with the audit trail we create. We're using code first EF 4.3.

    Overriding the SaveChanges method in our class derived from DbContext would allow us to call the stored procedure... however, the first thing the Entity Framework (or the underlying ADO.Net) does is call exec sp_reset_connection which I believe clears the CONTEXT_INFO. So that is a no go.

    As an alternative, within the DbContext derived class' constructor we can register for the connections state change event and watch fo the connection being opened:

       Database.Connection.StateChange += Connection_StateChange;

    Where Connection_StateChange is along the lines of:

            private void Connection_StateChange(object sender, StateChangeEventArgs e)
            {
                if (e.OriginalState == ConnectionState.Open || e.CurrentState != ConnectionState.Open)
                    return;

                DbCommand command = Database.Connection.CreateCommand();
                ....
                command.ExecuteNonQuery();

            }

    This seems to work well, setting the CONTEXT_INFO right before the Entity Framewok SQL executes. My only worry is that by registereing the DbContext class against the event, will it ever get released and garbage collected? When running in the VS2010 profiler my DbContext classes were hanging around (this is a web app)... however, according to the Object Lifetime table, no objects in the netire system were garbage collected (a call to to GC.Collect() does not actually force the collection).

    So, I'm wondering if anyone else knows what the lifecycle of the Connection object retrieved through Database.Connection? Is it only alive for the same length of time as the DbContext is being used or is its liftime linked to the underlying connection from the connection pool?

    Tuesday, May 15, 2012 11:15 PM

Answers

  • Hi Piers Lawson,

    Sorry for my late reply. The modified code is well. The principle of using connection is open only before operation being exected and close timely after operation completing. Based on the code you posted, it seems no problem. For your original question, yes, as far as I know, if you want to write a customer logic before EF hit the database, this is the only way.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Piers Lawson Tuesday, May 22, 2012 12:37 PM
    Tuesday, May 22, 2012 3:33 AM
    Moderator

All replies

  • Hi Piers Lawson,

    Welcome to MSDN Forum.

    We will do some more pending research  about your problem and come back as soon as possible. Thanks for understanding.

    Have a nice day.


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 17, 2012 5:06 AM
    Moderator
  • Thank you for looking into this.

    I have realised that I may be mistaken about not being able to use the override of StateChange approach, as having an override along the lines of:

            public override int SaveChanges()
            {
                // Open the connection early and leave it open
                Database.Connection.Open();

                var command = Database.Connection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;

                ...

                command.ExecuteNonQuery();

                return base.SaveChanges();
            }

    Does seem to execute the stored procedure and the EF generated SQL without a call to  sp_reset_connection in between. So a second question: would this approach work consistently or are there occasions when the reset may still happen?

    I would still like an answer to the original question as that aproach appears to be the only way to be able to set CONTEXT_INFO consistently for all connections regardless of whether they are going to be used for a Read or a Write of data.

    Thank you

    Piers


    Thursday, May 17, 2012 7:03 AM
  • Hi Piers Lawson,

    Sorry for my late reply. The modified code is well. The principle of using connection is open only before operation being exected and close timely after operation completing. Based on the code you posted, it seems no problem. For your original question, yes, as far as I know, if you want to write a customer logic before EF hit the database, this is the only way.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Piers Lawson Tuesday, May 22, 2012 12:37 PM
    Tuesday, May 22, 2012 3:33 AM
    Moderator