Logging every data change with Entity Framework

Question

There is a need from a customer to log every data change to a logging table with the actual user who made the modification. The application is using one SQL user to access the database, but we need to log the "real" user id.

We can do this in t-sql by writing triggers for every table insert and update, and using context_info to store the user id. We passed the user id to a stored procedure, stored the user id in the contextinfo, and the trigger could use this info to write log rows to the log table.

I can not find the place or way where or how can I do something similar using EF. So the main goal is: if I make a change in the data via EF, I would like to log the exact data change to a table in a semi-automatic way (so I don't want to check for every field for change before saving the object). We are using EntitySQL.

Unfortunately we have to stick on SQL 2000 so the data change capture introduced in SQL2008 is not an option (but maybe that's also not the right way for us).

Any ideas, links or starting points?

[Edit] Some notes: by using ObjectContext.SavingChanges eventhandler, I can get the point where I can inject the SQL statement to initialize the contextinfo. However I cannot mix the EF and the standard SQL. So I can get the EntityConnection but I cannot execute a T-SQL statement using it. Or I can get the connection string of the EntityConnection and create an SqlConnection based on it, but it will be a different connection, so the contextinfo will not affect the save made by the EF.

I tried the following in the SavingChanges handler:

testEntities te = (testEntities)sender;
DbConnection dc = te.Connection;
DbCommand dcc = dc.CreateCommand();
dcc.CommandType = CommandType.StoredProcedure;
DbParameter dp = new EntityParameter();
dp.ParameterName = "userid";
dp.Value = textBox1.Text;
dcc.CommandText = "userinit";
dcc.Parameters.Add(dp);
dcc.ExecuteNonQuery();

Error: The value of EntityCommand.CommandText is not valid for a StoredProcedure command. The same with SqlParameter instead of EntityParameter: SqlParameter cannot be used.

StringBuilder cStr = new StringBuilder("declare @tx char(50); set @tx='");
cStr.Append(textBox1.Text);
cStr.Append("'; declare @m binary(128); set @m = cast(@tx as binary(128)); set context_info @m;");

testEntities te = (testEntities)sender;
DbConnection dc = te.Connection;
DbCommand dcc = dc.CreateCommand();
dcc.CommandType = CommandType.Text;
dcc.CommandText = cStr.ToString();
dcc.ExecuteNonQuery();

Error: The query syntax is not valid.

So here I am, stuck to create a bridge between Entity Framework and ADO.NET. If I can get it working, I will post a proof of concept.

Answer 1

Thanks for pointing me in the right direction. However, in my case, I also need to set the context info when doing select statements, because I am querying views that use the context info to control row-level security by user.

I found it easiest to attach to the StateChanged event of the connection and just watch for the change from not-open to open. Then I call the proc that sets context and it works every time, even if EF decides to reset the connection.

private int _contextUserId;

public void SomeMethod()
{
    var db = new MyEntities();
    db.Connection.StateChange += this.Connection_StateChange;
    this._contextUserId = theCurrentUserId;

    // whatever else you want to do
}

private void Connection_StateChange(object sender, StateChangeEventArgs e)
{
    // only do this when we first open the connection
    if (e.OriginalState == ConnectionState.Open ||
        e.CurrentState != ConnectionState.Open)
        return;

    // use the existing open connection to set the context info
    var connection = ((EntityConnection) sender).StoreConnection;
    var command = connection.CreateCommand();
    command.CommandText = "proc_ContextInfoSet";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("ContextUserID", this._contextUserId));
    command.ExecuteNonQuery();
}

Answer 2

This is what I used found here I modified it because it didn't work

private object GetPrimaryKeyValue(DbEntityEntry entry)
        {
            var objectStateEntry = ((IObjectContextAdapter)this).ObjectContext.ObjectStateManager.GetObjectStateEntry(entry.Entity);
            object o = objectStateEntry.EntityKey.EntityKeyValues[0].Value;
            return o;
        }

         private bool inExcludeList(string prop)
        {
            string[] excludeList = { "props", "to", "exclude" };
            return excludeList.Any(s => s.Equals(prop));
        }

        public int SaveChanges(User user, string UserId)
        {
            var modifiedEntities = ChangeTracker.Entries()
                .Where(p => p.State == EntityState.Modified).ToList();
            var now = DateTime.Now;

            foreach (var change in modifiedEntities)
            {

                var entityName = ObjectContext.GetObjectType(change.Entity.GetType()).Name;
                var primaryKey = GetPrimaryKeyValue(change);
                var DatabaseValues = change.GetDatabaseValues();

                foreach (var prop in change.OriginalValues.PropertyNames)
                {
                    if(inExcludeList(prop))
                    {
                        continue;
                    }

                    string originalValue = DatabaseValues.GetValue<object>(prop)?.ToString();
                    string currentValue = change.CurrentValues[prop]?.ToString();

                    if (originalValue != currentValue)
                    {
                        ChangeLog log = new ChangeLog()
                        {
                            EntityName = entityName,
                            PrimaryKeyValue = primaryKey.ToString(),
                            PropertyName = prop,
                            OldValue = originalValue,
                            NewValue = currentValue,
                            ModifiedByName = user.LastName + ", " + user.FirstName,
                            ModifiedById = UserId,
                            ModifiedBy = user,
                            ModifiedDate = DateTime.Now
                        };

                        ChangeLogs.Add(log);
                    }
                }
            }
            return base.SaveChanges();
        }



public class ChangeLog 
    {
        public int Id { get; set; }
        public string EntityName { get; set; }
        public string PropertyName { get; set; }
        public string PrimaryKeyValue { get; set; }
        public string OldValue { get; set; }
        public string NewValue { get; set; }
        public string ModifiedByName { get; set; }



        [ForeignKey("ModifiedBy")]
        [DisplayName("Modified By")]
        public string ModifiedById { get; set; }
        public virtual User ModifiedBy { get; set; }


        [Column(TypeName = "datetime2")]
        public DateTime? ModifiedDate { get; set; }
    }