I am looking for a way to override the SaveChanges method/process in EF. We need to grab the SQL somehow, prevent the normal update/delete/insert from executing, and use that generated SQL to run our custom procedure.
- Call
SaveChanges()as normal. Let EF generate the SQL. - Get the SQL
- Prevent that SQL from being executed the normal way
- Call a custom stored procedure (takes additional params, etc)
- Pretend like we executed
SaveChanges(or just return 0)
The only real problem I see is grabbing the SQL from inside the SaveChanges method. What we would do is something like this, ideally...
- Get provider / connection / etc
- Setup event hooks to handle this
- Done, no code changes / overrides, etc.
We are using MVC4 & EF5 against an 3 letter acronym's database. The point here is to avoid hand-coding SQL in each update action, and rely on EF to generate all that for us. Since the procedure takes straight SQL
Yes, this is not a good way to do it (the single procedure) but we have no choice in the matter. None whatsoever. If we can't do this then we will need to write the custom sql. Perhaps there is another way that we can enforce this, where we pass the context and do the work ourselves? Then we can just audit that 'SaveChanges()' is never called :D
Solution
I used the EFTracingProvider as a starting point to create my own provider that does this (and some other things). You can also do it with only the EFTracingProvider by placing everything in your Entities class and handling events. You won't see your modified SQL since this event will fire after it, so you need to do your own logging. This has been stripped down to better fit in the website :)
public class MyEntities : MyBaseEntities
{
public MyEntities(): this(connectionString: "name=MyBaseEntities") {}
public MyEntities(string connectionString)
: base(MakeConnection(connectionString, "EFTracingProvider")) {}
/// <summary>
/// Insert the wrapped connection by calling the base toolkit.
private static EntityConnection MakeConnection(string connectionString, params string[] providers)
{
var conn = EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
connectionString,
providers
);
//get the tracing connection, so that we can attach event handlers
var us = conn.UnwrapConnection<EFTracingConnection>();
if (us != null)
{
us.CommandExecuting += BeforeExecute;
}
return conn;
}
private static void BeforeExecute(object sender, CommandExecutionEventArgs e)
{
// If an Create/Update/Delete action then we need to wrap it in our custom proc
if (IsCudAction(e.CommandTree))
{
var text = cmd.Parameters.Cast<DbParameter>().Aggregate(
cmd.CommandText,
(current, p) => current.Replace(p.ParameterName, SafeSql.Prepare(p.Value)));
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[ExecuteForMe]";
cmd.Parameters.Clear();
cmd.Parameters.AddRange(new[]
{
new SqlParameter("commandText", text),
new SqlParameter("extraInfo", "logging context")
});
}
}
public static bool IsCudAction(DbCommandTree commandTree)
{
if (commandTree is DbUpdateCommandTree) return true;
if (commandTree is DbDeleteCommandTree) return true;
if (commandTree is DbInsertCommandTree) return true;
if (commandTree is DbQueryCommandTree) return false;
if (commandTree is DbFunctionCommandTree) return false;
throw new InvalidOperationException("Unknown type of CommandTree: " + commandTree.GetType().Name);
}
}
