CODEWITHSUNDEEP
oracle-databaseentity-frameworkentity-framework-4dbcontextodp.net
Tom Halladay
Tom Halladay

Reputation: 5761

Setting Oracle session variables prior to database read/write events in Entity Framework / ODP.NET

I have a situation where I need to set oracle session variables/context before read & write events happen (this is a requirement by the client and not negotiable).

Where would be the best place to insert this code? I'm already overriding the DBContext.New() method as well as DBContext.OnModelCreating() for other purposes, but I can't tell if either of them are the true beginning of the entire database session (since EF is stateless...?)

Pretty sure the actual code would be a simple Me.Database.SqlQuery(), just need to be sure of where to put it.

My perceived options:

1

Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
    [Other Code]

    MyBase.OnModelCreating(modelBuilder)

    Me.Database.ExecuteSqlCommand("SESSIONCOMMAND")
End Sub

2

Using CTX As New MyContext()
    CTX.Database.ExecuteSqlCommand("SESSIONCOMMAND")

    [OTHER CODE]
End Using

3

Public Sub New()
    MyBase.New(
        New OracleConnection(
            ConfigurationManager.ConnectionStrings("MyContext").ConnectionString),
         True)

    Me.Database.ExecuteSqlCommand("SESSIONCOMMAND")
End Sub

I'm pretty sure #3 is not possible because EF opens the connection after this sub, so I can't execute a command against it yet at this point.

Upvotes: 4

Views: 1095

Answers (1)

Mike Peterson
Mike Peterson

Reputation: 1099

You could try doing it when the connection state changed from closed to open. Like this...

public partial class MyContext : DbContext
{
    public MyContext()
    {
        this.Database.Connection.StateChange += Connection_StateChange;
    }

    void Connection_StateChange( object sender, System.Data.StateChangeEventArgs e )
    {
        if ( e.CurrentState == System.Data.ConnectionState.Open )
        {
            if ( sender is System.Data.Common.DbConnection )
            {
                var command = ( sender as System.Data.Common.DbConnection ).CreateCommand();
                command.CommandText = "SESSIONCOMMAND";
                command.CommandType = System.Data.CommandType.Text;
                command.ExecuteNonQuery();
            }
        }
    }

Upvotes: 1

Related Questions