title: Logging every data change with Entity Framework
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p>Any ideas, links or starting points?</p>
  
  <p><strong>[Edit]</strong>
  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.</p>
  
  <p>I tried the following in the SavingChanges handler:</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>Error: The value of EntityCommand.CommandText is not valid for a StoredProcedure command. 
  The same with SqlParameter instead of EntityParameter: SqlParameter cannot be used.</p>
  
  <pre><code>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();
  </code></pre>
  
  <p>Error: The query syntax is not valid.</p>
  
  <p>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.</p>