ProfiledConnection should use IAsyncProfilerProvider instead of IDbProfiler

[sebastienros]

IAsyncProfilerProvider allows to define a custom lifetime for a MiniProfiler instance but the ProfiledConnection constructor will inherently reuse the same instance for the lifetime of the connection. If the connection and the IAsyncProfilerProvider implementations don't have the same matching lifetime then it doesn't work anymore.

For instance, with Sqlite you can reuse the same connection over http requests, so the ProfiledConnection instance will use the MiniProfiler.Current of the first request, and all new timings will be ignored for subsequent requests.

Another solution is to just use DefaultOptions.ProfilerProvider directly from ProfiledConnection instead of passing a provider, then and use it every time.

Note: I mitigated the issue by implementing a custom profiled connection as explained, with the only caveat that miniProfiler.IsActive is internal.

[NickCraver]

I'd wager most use cases don't have this issue, but in any case a behavior change here would be a major breaking change and reserved for 5.x (and something I tried to prevent by not changing the IDbProfiler interface in 4.x either). At Stack, we implement IDbProfiler in other ways disconnected from MiniProfiler code except for the ADO.NET capture layer of connection/command/etc...so there are definitely non-profiler, non-storage use cases (e.g. we track activity and report telemetry on it back to the load balancer).

My suggestion here would be to implement a shim IDbProfiler - that's the intended disconnect point. Here's an example:

private class CurrentDbProfiler : IDbProfiler
{
    private Func<IDbProfiler> GetProfiler { get; }
    public CurrentDbProfiler(Func<IDbProfiler> getProfiler) => GetProfiler = getProfiler;

    public bool IsActive => GetProfiler()?.IsActive ?? false;

    public void ExecuteFinish(IDbCommand profiledDbCommand, SqlExecuteType executeType, DbDataReader reader) =>
        GetProfiler()?.ExecuteFinish(profiledDbCommand, executeType, reader);

    public void ExecuteStart(IDbCommand profiledDbCommand, SqlExecuteType executeType) =>
        GetProfiler()?.ExecuteStart(profiledDbCommand, executeType);

    public void OnError(IDbCommand profiledDbCommand, SqlExecuteType executeType, Exception exception) =>
        GetProfiler()?.OnError(profiledDbCommand, executeType, exception);

    public void ReaderFinish(IDataReader reader) => GetProfiler()?.ReaderFinish(reader);
}

You can use such a proxy (1 instance) when creating your ProfiledDbConnection. For example in your case if you're just wanting to point to the current MiniProfiler:

var conn = new ProfiledDbConnection(new SqliteConnection("..."), new CurrentDbProfiler(() => MiniProfiler.Current));

I added a test to demonstrate this in the test suite here - hope that helps!

[NickCraver]

Approach to solve this is above - going to close this out to cleanup!

[sebastienros]

I implemented your approach and it worked well, thanks.