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')";
}
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);