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?

Looks like it only serializes non abstract properties. You can try by changing dbcommand to sqlcommand and see if that works. You can also try using your own abstract class to serialize to confirm this behavior — Chetan, Jun 04 '17 at 04:19
@ChetanRanpariya a custom serializer seems like the way to go :) — Maria Ines Parnisari, Jun 04 '17 at 21:36

Maria Ines Parnisari · Answer 1 · 2017-06-04T20:30:08.103

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

Hi @Maria! I tried your solution for SqlDbType.Structured parameter, but did not work. What could I do? — cnom, Jun 25 '19 at 13:47

Serializing DbCommand with Newtonsoft.Json

1 Answers1