JSON format is non-standard?

[diddlys]

I'm trying to write JSON rendered from an object to the MSSqlServer sink. I'm also logging to the File sink and the JSON is written correctly there, so I'm at a loss.

Here is the JSON going in to my database column - note that the object type is pre-pended, and the field names are not in quotes as they should be:

AuditRecord { Id: 1494, secondId: 56789, entity: "dbo.TableName", action: "Update", fields: [Field { field: "startDate", oldValue: "2018-01-01", newValue: "2018-01-02" }, Field { field: "reg", oldValue: "abc123", newValue: "def345" }], user: "userguid", timestamp: 05/04/2023 17:52:28, reason: "reasoncode" }

I don't understand how this format is being rendered. The same object is rendered as this by the File sink:

{"Id":1494,"secondId":56789,"entity":"dbo.Vehicle","action":"Update","fields":[{"field":"startDate","oldValue":"2018-01-01","newValue":"2018-01-02","$type":"Field"},{"field":"reg","oldValue":"abc123","newValue":"def345","$type":"Field"}],"user":"userguid","timestamp":"2023-05-04T17:52:28.1177133+01:00","reason":"reasoncode","$type":"AuditRecord"}

Why would the database sink be doing this, or where in the pipeline might this be happening?

[jonorossi]

Here is the JSON going in to my database column - note that the object type is pre-pended, and the field names are not in quotes as they should be:

Looks a bit like a call to ToString with the class name and no double quoted fields.

Why would the database sink be doing this, or where in the pipeline might this be happening?

Can you show the code you are using to log this and include the type information if you are just passing a JSON type to Serilog.

[diddlys]

Thanks for getting back so quickly.

Here is some code, variable names anonymised - I'm running as NUnit tests for expediency while I try to get this working as I want:

    [SetUp]
    public void SetUp()
    {
        var sqlSinkOptions = new MSSqlServerSinkOptions { TableName = "AuditRecord", SchemaName = "MOT" };
        var columnOptions = new ColumnOptions
        {
            AdditionalColumns = new List<SqlColumn>
            {
                new SqlColumn
                {
                    ColumnName = "AuditJSON",
                    DataType = SqlDbType.NVarChar,
                    DataLength = -1,
                    AllowNull = true,
                    PropertyName = "AuditJSON"
                }
            }
        };

        columnOptions.Store.Remove(StandardColumn.Message);
        columnOptions.Store.Remove(StandardColumn.MessageTemplate);
        columnOptions.Store.Remove(StandardColumn.Level);
        columnOptions.Store.Remove(StandardColumn.TimeStamp);
        columnOptions.Store.Remove(StandardColumn.Exception);
        columnOptions.Store.Remove(StandardColumn.Properties);

        Log.Logger = new LoggerConfiguration()
            .ReadFrom.AppSettings()
            .WriteTo.Conditional(
                evt => evt.Properties.ContainsKey("UseSQLLog"),
                wt => wt.MSSqlServer(
                    "TransportConnection"
                    , sinkOptions: sqlSinkOptions
                    , columnOptions: columnOptions
                    , logEventFormatter: new JsonFormatter()))
            .CreateLogger();

        Serilog.Debugging.SelfLog.Enable(msg => Debug.WriteLine(msg));
    }

    [Test]
    public void ShouldWriteSerilog()
    {
        // Arrange
        var newRecord = new AuditRecord
        {
            id = 1494
            ,
            szecondId = 56789
            ,
            entity = "dbo.TableName"
            ,
            action = "Update"
            ,
            fields = new Field[]
                {
                    new Field { field = "startDate", oldValue = "2018-01-01", newValue = "2018-01-02" }
                    , new Field { field = "reg", oldValue = "abc123", newValue = "def345" }
                }
                ,
            user = "userguid"
                ,
            timestamp = DateTime.Now
                ,
            reason = "reasoncode"
        };

        // Act
        Log.Information("log to file");
        Log.ForContext("UseSQLLog", true).Information("{@AuditJSON}", newRecord);
        Log.CloseAndFlush();

        // Assert
    }

I have amended and got what I need using Newtonsoft - I'm just curious as to why the JSON format is different between the two sinks:

        string json = JsonConvert.SerializeObject(newRecord);
        Log.ForContext("UseSQLLog", true).Information("{AuditJSON}", json);