ORMLite: DBNull serialized as {} when using ToInsertStatement

[steve-solcast]

We implemented a Upsert method (for Postgres INSERT ON CONFLICT UPDATE) and found that using the ToInsertStatement caused null values to become {} instead of the expected NULL.

This was solved by implementing a DBNull ReferenceTypeConverter below

` ... PostgreSqlDialect.Provider.RegisterConverter(new DBNullConverter()); ....

public class DBNullConverter : ReferenceTypeConverter
{
    public override string ToQuotedString(Type fieldType, object value)
    {
        return "null";
    }
}

`

[Layoric]

Hey @steve-solcast, it sounds like you have a reference type in your table class which is serializing to a JSON or text column? Glad you've got a work around for now, but if you could include code (DTO and statement used to generate the SQL should be good) so we can reproduce the specific issue so we can look at addressing it. Thanks!

[steve-solcast]

Hay @Layoric, Yes the DBNull object is s reference type. This comes from the GetValueOrDbNull method in OrmLiteDialectProviderBase when the field value returns NULL, which is called from SetParameterValue and SetParameterValues, ToInsertStatement

Thus servicestack is converting NULL (in the Modified property) into DBNull, which is then serialized as a ReferenceType

` public static int Upsert(IDbConnection db, T item, List constraintColumns) { var insertStatement = db.ToInsertStatement(item); var modelDef = typeof(T) .GetModelMetadata();

        constraintColumns = constraintColumns.Select(x => x.Quoted()).ToList();

        var onConflictPart = $" ON CONFLICT ({constraintColumns.Join(",")}) DO UPDATE ";
        var updateColumns = modelDef.FieldDefinitions.Where(x => !constraintColumns.Contains(PostgreSqlDialect.Provider.GetQuotedColumnName(x.FieldName)) && !x.IsPrimaryKey)
            .Select(x => PostgreSqlDialect.Provider.GetQuotedColumnName(x.FieldName)).ToList();

        var setPart = "SET ";
        for (var index = 0; index < updateColumns.Count; index++)
        {
            var updateColumn = updateColumns[index];
            setPart = setPart + updateColumn + " = EXCLUDED." + updateColumn;
            if (index == updateColumns.Count - 1)
                setPart += ";";
            else
                setPart += ",";
        }

        var statement = insertStatement + onConflictPart + setPart;
        return db.ExecuteNonQuery(statement);
    }

using (var db = dbFactory.Open()) { Upsert<SolcastProduct>(db, new SolcastProduct { Id = "timeXXXX", Name = "Timeseries", Status = ProductStatus.Inactive, Created = DateTime.UtcNow }, new List<string>{ "Id" }); } `

`
    public class SolcastProduct
    {
        public string Id { get; set; }

        public string Name { get; set; }

        public ProductStatus Status { get; set; }

        public DateTime Created { get; set; }

        public DateTime? Modified { get; set; }

        public string StripProductId { get; set; }

        public string[] Parameters { get; set; }
    }
`

[steve-solcast]

Also, simply using db.Insert(solcastProduct) works successfully without any equivalent issue. So it seems the ToInsertStatement works differently to an Insert call.

[Layoric]

Thanks. I've added the missing value check for DBNull in this commit and a couple of tests to confirm behavior fix.

This change is available from the latest v5.10.5 that's now available on MyGet.