Can I fire an event on connect database in Entity Framework Core?

Question

I've one DbContext with all working to access my Postgresql DB, but I need to run one little SQL command when connection session starts with DB. I need to do this for every interaction. To be more specific, it's a function for set a session variable with user name logged.

It's possible to do something to handle that in EF Core?

--SOLUTION--

I didn't realized that I could specify a connection directly in OnConfiguring like bricelam says. I need to do this in every connection because it's a variable by session. It's not a user name for database but for application logging system.

    public ContratoInternetDbContext(DbContextOptions<ContratoInternetDbContext> options, 
        IOptions<AppSettings> configs)
        : base(options)
    {
        _appSettings = configs.Value;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var conn = new NpgsqlConnection(_appSettings.ConnectionString);
        conn.StateChange += (snd, e) =>
        {
            if ((e.CurrentState != e.OriginalState) && (e.CurrentState == ConnectionState.Open))
            {
                _cmmSetVarSession.ExecuteNonQuery();
            }
        };

        optionsBuilder.UseNpgsql(conn);

        _cmmSetVarSession = conn.CreateCommand();
        _cmmSetVarSession.CommandText = "select sessao_set_var('usuario', 'CENTRAL_CLIENTE')";
    }

Answer 1

If you are using EF Core 3.0 or greater you can now use a DBConnectionInterceptor for this. The code to do what you want looks something like this.

public class DbUserIdProvider : DbConnectionInterceptor
{
    // Called just after EF has called Open().
    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        base.ConnectionOpened(connection, eventData);
        var cmd = connection.CreateCommand();
        cmd.CommandText = "set session.client_user_id to 'myid'";
        cmd.ExecuteNonQuery();
    }

    // Called just after EF has called OpenAsync().
    public override Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        var cmd = connection.CreateCommand();
        cmd.CommandText = "set session.client_user_id to 'myid'";
        cmd.ExecuteNonQuery();
        return base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}

ConnectionOpenedAsync() is called when you use an async method to access the database.

To wire in this interceptor so the context knows about it, you have to add it to the AddDbContext call in Startup.cs like this.

services.AddDbContext<ApplicationDbContext>(options => options
            .UseNpgsql(connection)
            .AddInterceptors(new DbUserIdProvider()));

EDIT: Since I wrote this I have found a better way to add the interceptor to the DB context. I now do it like this.

services.AddScoped<DbUserIdInterceptor>();

services.AddDbContext<ApplicationDbContext>((provider, options) =>
{
    options.UseNpgsql(applicationSettings.ConnectionString());
    // Resolve the DbUserIdInterceptor from the service provider
    options.AddInterceptors(provider.GetRequiredService<DbUserIdInterceptor>());
});

This allows me to use constructor injection for DI in the Interceptor, which now looks like this.

public class DbUserIdInterceptor : DbConnectionInterceptor
{
    UserInfoService userInfoService;
    public DbUserIdInterceptor(UserInfoService uis)
    {
        userInfoService = uis;
    }

    // Called just after EF has called OpenAsync().
    public override Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        var cmd = connection.CreateCommand();
        cmd.CommandText = $"set session.client_user_id to '{userInfoService.uniqueName}'";
        cmd.ExecuteNonQuery();
        return base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }
}

Answer 2

You should be able to do it by passing a connection into your DbContext and hooking the StateChange event: (Please forgive the SQLite example. I know you said PostgreSQL.)

var connection = new SqliteConnection(connectionString);
_connection.StateChange += (sender, e) =>
{
    if (e.OriginalState != ConnectionState.Open)
        return;

    var senderConnection = (DbConnection)sender;

    using (var command = senderConnection.CreateCommand())
    {
        command.Connection = senderConnection;
        command.CommandText = "-- TODO: Put little SQL command here.";

        command.ExecuteNonQuery();
    }
};

optionsBuilder.UseSqlite(connection);