Serializing DbCommand with Newtonsoft.Json

Question

I am trying to serialize Entity Framework DbCommand to json with Newtonsoft.Json:

public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
   var path = "C:/result.json";
   File.WriteAllText(path, JsonConvert.SerializeObject(command));
}

However, in result json file the parameters are ToString() values rather than objects:

"Parameters": ["@0", "@1", "@2"]

How do I tell the serializer to serialize the whole objects?

Answer 1

You can log the actual values using a custom JsonConverter.

First, the sample query:

using (var context = new SchoolDbContext("dbConnectionString"))
{
     var id = 11;
     var dni = 36170503;
     var sql = @"SELECT name FROM dbo.students WHERE Id = {0} OR Dni = {1}";
     context.Database.ExecuteSqlCommand(sql, id, dni);
} 

Then, add a custom EF interceptor as follows:

public class MyDBConfiguration : DbConfiguration
{
    public MyDBConfiguration()
    {
        DbInterception.Add(new EntityFrameworkInterceptor());
    }
}


public class EntityFrameworkInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        var path = @"C:\Users\miparnisari\Documents\log.json";
        var serialized = JsonConvert.SerializeObject(command, new DbCommandSerializer());
        File.WriteAllText(path, serialized);
    }
}

Finally, the serializer:

public class DbCommandSerializer : JsonConverter
{
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        var dbCommand = value as DbCommand;
        writer.Formatting = Formatting.Indented;
        writer.WriteStartObject();
        writer.WritePropertyName("Query");
        writer.WriteValue(dbCommand.CommandText);
        writer.WritePropertyName("Parameters");
        writer.WriteStartObject();
        foreach (DbParameter param in dbCommand.Parameters)
        {
            writer.WritePropertyName(param.ParameterName);
            writer.WriteValue(param.Value);
        }

        writer.WriteEndObject();
        writer.WriteEndObject();
        writer.Flush();
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override bool CanConvert(Type objectType)
    {
        return typeof(DbCommand).IsAssignableFrom(objectType);
    }
}

This logs:

{
  "Query": "SELECT name FROM dbo.students WHERE Id = @p0 OR Dni = @p1",
  "Parameters": {
    "p0": 11,
    "p1": 36170503
  }
}