Possible to get statistics back for DbContext.SaveChanges?

[joshmouch]

I'd like to write some automated tests to measure how much database IO is occurring during particular processes in our application and ensure they never go above a certain threshold.

Outside of EF, you would:

1. Open SQL Server Management Studio
2. set io statistics on
3. ...run your queries
4. ... look at the response returned in the Messages window see the statistics.

So I'm wondering if there is some way to get these same measurements as part of the call to DbContext.SaveChanges. I think you could override the SaveChangesAsync and SaveChanges methods to execute the "set io statistics" statement, but doing it that way would result in an extra database hit and also I wouldn't know how to get the statistics back after SaveChangesAsync was complete.

Any thoughts or ideas?

[ajcvickers]

@joshmouch We discussed this and we don't have any really good answers. It seems to us that overriding SaveChanges and issuing the commands is probably the best way to go--yes there will be some additional overhead, but collecting the statistics is also adding overhead, and we're expecting this is done in dev/debug anyway. I'm not sure how you would get the statistics back, but you can use the underlying SqlConnection object after SaveChanges to query the database for whatever is needed, so if you figure out how to do this outside of EF, then the same thing should work with EF.

[windhandel]

Greetings @ajcvickers,

I've attempted to do something similar to the OP by implementing IDbCommandInterceptor within EF Core.

However, despite how I have attempted to attach to the connection's InfoMessage event, I cannot seem to receive the events themselves.

I've dumbied up a simple app to confirm that it should work as I had expected outside of EF Core. This seems to follow your stated principle of "what should work outside EF Core, should work inside". Have I overlooked something in my implementation or expectations?

`
public class BareTrackingCommandInterceptor : DbCommandInterceptor, IDbCommandInterceptor
{
bool attached = false;

    public BareTrackingCommandInterceptor()
    {
    }

    void InfoMessageHandler(object sender, SqlInfoMessageEventArgs args)
    {
        Debug.WriteLine(args.Message);
    }

    void attachStatisticsTracking(SqlConnection sqlConnection)
    {
        if (!attached)
        {
            sqlConnection.InfoMessage += InfoMessageHandler;
            attached = true;
        }
    }

    void enableLogicalReads(SqlCommand command)
    {
        command.CommandText = "SET STATISTICS IO ON;" + command.CommandText;
    }

    public override DbCommand CommandCreated(CommandEndEventData eventData, DbCommand result)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return base.CommandCreated(eventData, result);
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return result;
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return result;
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return result;
    }

    public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return Task.FromResult(result);
    }

    public override Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return Task.FromResult(result);
    }

    public override Task<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        enableLogicalReads(eventData.Command as SqlCommand);
        attachStatisticsTracking(eventData.Connection as SqlConnection);
        return Task.FromResult(result);
    }
}

`

Thanks for any assistance you may offer.

Kind regards & Happy Thanksgiving,
Jeff

[windhandel]

Similarly, I attempted to implement an IDbConnectionInterceptor and none of the methods on it are being called, including:

• ConnectionOpening
• ConnectionOpeningAsync
• ConnectionOpened
• ConnectionOpenedAsync
• ConnectionClosing
• ConnectionClosingAsync
• ConnectionClosed
• ConnectionClosedAsync
• ConnectionFailed
• ConnectionFailedAsync

[roji]

@windhandel this issue is from 2017 and things have changed since - can you please open a new issue with a code sample where you use IDbConnectionInterceptor? That approach should work.

[windhandel]

Please see the new issue I created for the workaround for EF Core correctly causing the SqlConnection's InfoMessage event to be fired.