Musings on code and life

Entity Framework ExecutableLogFormatter

One of the things that annoys me about Entity Framework 6 logging is that it doesn't output the parameters to the sql statement in a way that can be easily executed in Sql Server if you want to test it out. So I wrote this simple ExecutableLogFormatter that outputs the sql and parameters in an executable format. I haven't tested this thoroughly but it is at least a start and worked for my purposes. It should be simple enough to extend and add, for example, more comprehensive type support if you need it.


--Example Output
DECLARE @Param1 varchar(10) = 'Hello'
DECLARE @Param2 varchar(10) = 'World'
EXEC MyProc @Param1, @Param2

//ExecutableLogFormatter.cs
public class ExecutableLogFormatter : DatabaseLogFormatter
{
    public ExecutableLogFormatter(DbContext context, Action<string> writeAction)
        : base(context, writeAction)
    {
    }

    public override void LogCommand<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        var sb = new StringBuilder();
        command.Parameters.Cast<SqlParameter>().Select(FormatParameter).Each(x => sb.AppendLine(x));
        sb.AppendLine(command.CommandText);
        Write(sb.ToString());
    }

    private static string FormatParameter(SqlParameter param)
    {
        var format = "-- Name:{0} Type:{1} Size:{2} Value:{3}";
        switch (param.SqlDbType)
        {
            case SqlDbType.NVarChar:
            case SqlDbType.VarChar:
                format = "DECLARE @{0} {1}({2}) = {3}";
                break;
            default:
                format = "DECLARE @{0} {1} = {3}";
                break;
        }

        return string.Format(format, param.ParameterName, param.SqlDbType, param.Size,
            param.Value is DBNull ? "NULL" : "'" + param.Value + "'");
    }
};

//MyDbConfiguration.cs
public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
#if DEBUG
        SetDatabaseLogFormatter((context, writeAction) => new ExecutableLogFormatter(context, writeAction));
#endif
    }
};