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

How about handling Context.SavingChanges?