JsonSerializer does not serialize DBNull.Value as null

[layomia]

using System;
using Newtonsoft.Json;
					
public class Program
{
	public static void Main()
	{
		Console.WriteLine(JsonConvert.SerializeObject(DBNull.Value)); // null
		Console.WriteLine(System.Text.Json.JsonSerializer.Serialize(DBNull.Value)); // {}
	}
}

cc @RandomGHUser, @SanderSade

[layomia]

From @RandomGHUser in https://github.com/dotnet/corefx/issues/39808:

This issue isn't completely fixed, as DBNull values exhibit the same issue, and should also be serialized as null.

    var list = new Dictionary<string, object>(){
               {"keyA", null},
               {"keyB", DBNull.Value},
               {"keyC", ""},
            };
            var _DNC3J = System.Text.Json.JsonSerializer.Serialize(list);
            var _NSJ = Newtonsoft.Json.JsonConvert.SerializeObject(list);

_DNC3J = "{"keyA":null,"keyB":{},"keyC":""}"
_NSJ = "{"keyA":null,"keyB":null,"keyC":""}"

[layomia]

DbNull.Value is not null . Looks like Newtonsoft.Json special-cases it https://github.com/JamesNK/Newtonsoft.Json/blob/936acbf622b51dcc2fbd214199d22c7a639d3c1b/Src/Newtonsoft.Json/Converters/DataTableConverter.cs#L68.

[ahsonkhan]

I would imagine creating and registering a custom JsonConverter<DbNull> would work as a workaround to this issue.

Would that be sufficient or is DbNull used in a lot of context, justifying having the JsonSerializer itself be aware of it and special case it?

[SanderSade]

I think the dev expectation here is that DbNull is always considered to be equal to null when serializing and handled as such. Newtonsoft.Json seems to have a lot of checks and tests for DbNull, and there is a lot of code depending on that.

From another perspective, I cannot imagine a scenario where "{}" (empty object, which DbNull.Value is serialized to) in JSON would carry any meaningful value, as there is no way to differentiate it from any other empty object.

[ahsonkhan]

@SanderSade, @RandomGHUser - what do you expect the deserialization to be for that? For Dictionary<string, object> DbNull wouldn't roundtrip anyway since we deserialize System.Object as a boxed JsonElement. Is roundtripping not a concern here? I am assuming DbNull never shows up as a type of a property within a POCO, correct (because I don't know what it means to new one up - it just has a singleton)?

When we deserialize null into System.Object, we wouldn't create return DbNull.Value, rather just set the object to null.

Also, in what context do you end up getting an instance of DbNull in your object graph to begin with, where the current serialization behavior is causing issues?

[SanderSade]

Starting from the last - it seems that a very common (see links in #682) use of SqlDataReader in APIs is fetching the results directly into List<Dictionary<string, object>>:

var result = new Dictionary<string, object>();
while (reader.Read())
{
	result.Add(Enumerable.Range(0, reader.FieldCount)
           .ToDictionary(reader.GetName, reader.GetValue));
}

With JSON.NET, any DbNull.Value values in the dictionary were serialized as null values. With System.Json.Text, the DbNull.Value is serialized into empty object, "{}".

For deserialization/roundtripping, I cannot see a valid scenario where an empty object would be preferable to null (literally, "there is an object, but we have no idea what it is"). In .NET, DbNull is a mostly a legacy artifact due to non-nullable value types (see Marc Gravell's comment, "I have yet to see an occasion where I need to represent two different "null" states, so DBNull is entirely redundant given that null already exists and has much better language and runtime support.").

[AartBluestoke]

sidenote to @layomia i don't think that codebranch was taken in your example -- if nullvalue.ignore was set it would return and not show the key or value, producing
_NSJ = "{"keyC":""}"

As there is a difference in the statement "the database holds the null value" and "c# has no value for this" I don't think that this is necessarily an error.

From a language point of view:
Debug.Assert(DBNull.Value.Equals(null)==false);// assert passes
I don't think the language's internal serializer should serialize an object to something that it isn't equal to. sharplab

In ORM-like scenarios where the application is king, and the database is just there to store its data, then there is little use for dbNull, but in other scenarios (interacting with a data warehouse, or other shared data store) then that difference between those two states should be important.

Marc Gravell's comment (linked above by SanderSade) says "i don't think there is a reason for anyone to use DbNull" -- that is a different conversation to "Given we are using DbNull, we should be automatically demoting them to normal null when we serialize them"

If there is no need for DbNull, shouldn't the argument be taken to the libraries that produce them from the database, not here inside the runtime? From a languange point of view we have an object that in memory.

Finally, given that we are trying to minimize the use of nulls within the language as a whole (null reference tracking etc in c#8) , should we consider encouraging people to use DbNull, not actual null?

[layomia]

Here's a converter for a workaround:

private class DBNullConverter : System.Text.Json.Serialization.JsonConverter<DBNull>
{
    public override DBNull Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
    {
        // Implement custom logic.
        // Debug.Assert(typeToConvert == typeof(DBNull));

        // if (reader.TokenType != JsonTokenType.Null)
        // {
        //     throw new JsonException();
        // }
        // return DBNull.Value;
        throw new NotSupportedException();           
    }

    public override void Write(Utf8JsonWriter writer, DBNull value, JsonSerializerOptions options)
    {
        writer.WriteNullValue();
    }
}

As stated above, I can't imagine a deserialization scenario where an object graph would contain a strongly-typed property of DBNull, but a converter could handle such a case.

[layomia]

One open question with DbNull is with options.IgnoreNullValues, and potentially per-property ignoring support (https://github.com/dotnet/corefx/issues/40600): if DBNull is serialized as null, should it semantically be considered null in the serializer and ignored when indicated in the options?

[steveoh]

This just surprised me. count me into the camp that agrees it should serialize to null and it isn't that important to round trip back to a dbnull as i think a null is ok.

[eiriktsarpalis]

I don't believe offering out-of-the-box support for every serializable BCL type is a design goal for STJ. Rooting more converter implementations has a detrimental effect on size in trimmed apps, so we need to be selective about what types we do support. I defer to @layomia on whether we should still pursue adding a converter for DbNull. In any case it is fairly trivial to register a custom converter.

One open question with DbNull is with options.IgnoreNullValues, and potentially per-property ignoring support

Might be possible to address on the converter level provided that something like #55781 has been implemented.

[layomia]

At this point it would be a breaking change to change the serialized output here. It would be better for users to handle the behavior using a custom converter.