Overriding the EntityFramework SaveChanges - Grab the SQL and call a custom proc

Question

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...

1. Get provider / connection / etc
2. Setup event hooks to handle this
3. 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);
    }
}

Answer 1

Seems that it is perhaps possible to get the SQL using EF Tracing Provider... see the last post in this link

Answer 2

As I said in the comment, you can map crud actions to stored procedures (well, CUD actions). Yes, it takes a lot of work to write, map and maintain these sprocs, but possible gains are better performance and security. That's why DBA's like them so much. It might even be the main reason why this central monolith procedure in your database was created. There may be a way to use "CUD sprocs" to meet the same requirements.