Oracle Auditing (using CLIENT_IDENTIFIER) with MVC 3 & StructureMap & EntityFramework 4

REQUIREMENT:
We had a requirement to Audit CRUD operations on certain tables in our application.

Specifically: everytime a user Created, Updated or Deleted a Product, a row needed to be inserted into table Product_Audit. The record needs to capture, amoung other standard Audit fields, the UserId of the Application user (note: not the database user) who completed the operation.

ENVIRONMENT:
The application is an ASP.Net MVC 3 Web App, deployed on IIS 6, which utilises an Oracle 11g database.

The application utilises EntityFramework to facilitate the communication with the database.

The application also uses StructureMap as an Inversion of Control container.

Uses the Repository pattern

A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes

to place another layer of abstraction around the data modelling layer (EntityFramework).

SOLUTION:
Use Triggers to insert the relevant records in the Audit table upon CRUD operations on the relevant tables.
Within the Trigger, utilise the

SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')

function in order to retrieve the value of the CLIENT_IDENTIFIER environment variable.

This value must be set by the application. There are a few posts around describing how to do this - some say you do it on the Connection String of the OracleConnection object - however, you will find that you'll soon be greeted with the exception:

"'ClientId' is an invalid connection string attribute".
Source: Oracle.DataAccess
StackTrace:    at Oracle.DataAccess.Client.OracleConnection.ParseConnectionString() ...

The only way we found to set this value safely and consistently is to explicitly set the 'ClientId' property of the OracleConnection object:

conn.ClientId = System.Security.Principal.WindowsIdentity.GetCurrent() == null ?
              "Anonymous" 
              : System.Security.Principal.WindowsIdentity.GetCurrent().Name;

HURDLES:
1.  Determing the best place to set the ClientId property in the applications code
> We need access to the OracleConnection object
> This however, is wrapped up in an EntityConnection object provided by the EntityFramework library
> The EntityConnection itself is wrapped up in the our custom MyAppContext class which extends the ObjectContext object
> The MyAppContext is wrapped up in our custom Repository object which implements our IRepostiory interface

[Pluggable("Default")]
    public class Repository : IRepository where TEntity : class

> Each MVC Controller maintains various private IRepository's which are injected with an instance of their respective concrete Repositories at runtime by StructureMap.
> These repostiories are then used to persist data to the Oracle database.

THEREFORE:
> In order to set the ClientId property of the underlying OracleConnection object we have to dig into the MyAppContext in order to expose the EntityConnection and then cast it back to the underlying connection, ie the OracleConnection

2. You can only set the ClientId property once the connection has been opened
> So we had to define a handler for the Connection.StateChange property, make sure the connection is indeed Open, and then set the ClientId property
> The only place we found to do this (so far) is in the constructor of the MyAppContex

        
public MyAppContext (string connectionString)
            : base(connectionString, ContainerName)
{
    this.ContextOptions.LazyLoadingEnabled = true;
    this.Connection.StateChange += Connection_StateChange;
}

/// 

/// Plugs into the state change of the connection so we can set the ClientId property of the underlying
/// OracleConnection object. The purpose of this is so that we can use the SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')
/// function in our Oracle Auditing Triggers. If we do not set the ClientId, 'CLIENT_IDENTIFIER'
/// will be 'null' and the trigger will attempt to insert a null value into a non-null column.
/// 
private void Connection_StateChange(object sender, StateChangeEventArgs e)
{
    // Only set the ClientId once the connection is Open; otherwise runtime exception occurs
    if (e.CurrentState != ConnectionState.Open) return;

    // Get access to the Connection object underlying the EntityConnection
    OracleConnection conn = (base.Connection as EntityConnection).StoreConnection 
                            as Oracle.DataAccess.Client.OracleConnection;

    if (null == conn) return;

    // Set the ClientId property of the OracleConnection object so that triggers can use 
    // the 'CLIENT_IDENTIFIER' environment variable
    conn.ClientId = WindowsIdentity.GetCurrent() == null ? "Anonymous" : WindowsIdentity.GetCurrent().Name;
            
    return;
}

KNOWN ISSUES / RISKS:
1. Current solution requires us to edit Template Generated Code - namely the MyAppContext class.
> We needed to do this in order to register a handler for the Connection.StateChange event.
> The fix to this problem would be to find another place to register the handler apart from the Constructor; this new location of registration would have to be in a partial method though...
> Then we could put this handler registration in a partial MyAppContext class and leave the Template Generated MyAppContext partial class untouched