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?

Answer 2

Have you tried adding the stored procedure to your entity model?

Answer 3

Finally with Craig's help, here is a proof of concept. It needs more testing, but for first look it is working.

First: I created two tables, one for data one for logging.

-- This is for the data
create table datastuff (
    id int not null identity(1, 1),
    userid nvarchar(64) not null default(''),
    primary key(id)
)
go

-- This is for the log
create table naplo (
    id int not null identity(1, 1),
    userid nvarchar(64) not null default(''),
    datum datetime not null default('2099-12-31'),
    primary key(id)
)
go

Second: create a trigger for insert.

create trigger myTrigger on datastuff for insert as

    declare @User_id int,
     @User_context varbinary(128),
     @User_id_temp varchar(64)

    select @User_context = context_info
     from master.dbo.sysprocesses
     where spid=@@spid

    set @User_id_temp = cast(@User_context as varchar(64))

    declare @insuserid nvarchar(64)

    select @insuserid=userid from inserted

    insert into naplo(userid, datum)
     values(@User_id_temp, getdate())

go

You should also create a trigger for update, which will be a little bit more sophisticated, because it needs to check every field for changed content.

The log table and the trigger should be extended to store the table and field which is created/changed, but I hope you got the idea.

Third: create a stored procedure which fills in the user id to the SQL context info.

create procedure userinit(@userid varchar(64))
as
begin
    declare @m binary(128)
    set @m = cast(@userid as binary(128))
    set context_info @m
end
go

We are ready with the SQL side. Here comes the C# part.

Create a project and add an EDM to the project. The EDM should contain the datastuff table (or the tables you need to watch for changes) and the SP.

Now do something with the entity object (for example add a new datastuff object) and hook to the SavingChanges event.

using (testEntities te = new testEntities())
{
    // Hook to the event
    te.SavingChanges += new EventHandler(te_SavingChanges);

    // This is important, because the context info is set inside a connection
    te.Connection.Open();

    // Add a new datastuff
    datastuff ds = new datastuff();

    // This is coming from a text box of my test form
    ds.userid = textBox1.Text;
    te.AddTodatastuff(ds);

    // Save the changes
    te.SaveChanges(true);

    // This is not needed, only to make sure
    te.Connection.Close();
}

Inside the SavingChanges we inject our code to set the context info of the connection.

// Take my entity
testEntities te = (testEntities)sender;

// Get it's connection
EntityConnection dc = (EntityConnection )te.Connection;

// This is important!
DbConnection storeConnection = dc.StoreConnection;

// Create our command, which will call the userinit SP
DbCommand command = storeConnection.CreateCommand();
command.CommandText = "userinit";
command.CommandType = CommandType.StoredProcedure;

// Put the user id as the parameter
command.Parameters.Add(new SqlParameter("userid", textBox1.Text));

// Execute the command
command.ExecuteNonQuery();

So before saving the changes, we open the object's connection, inject our code (don't close the connection in this part!) and save our changes.

And don't forget! This needs to be extended for your logging needs, and needs to be well tested, because this show only the possibility!

Answer 4

We had solve this problem in a different way.

1. Inherit a class from your generated entity container class
2. Make the base entity class abstract. You can do it by a partial class definition in a separate file
3. In the inherited class hide the SavingChanges method with your own, using the new keyword in the method definition
4. In your SavingChanges method: a, open an entity connection b, execute the user context stored procedure with ebtityclient c, call base.SaveChanges() d, close the entityconnection

In your code you have to use the inherited class then.