SQLite: align JSON and M.D.Sqlite representations

[roji]

We seem to have some discrepancies between the string representation produced by our client-side JSON infrastructure (JsonValueReaderWriter), and the textual representation that M.D.Sqlite serializes to. For example, it seems like our JSON representation preserves zeros (the database contains 12:30:45.0000000), while Microsoft.Data.SQLite does not (we send a parameter with 12:30:45).

See TimeOnlyTypeTest.Query_property_within_json, ByteArrayTypeTest.Query_property_within_json. Also consider increasing type test coverage for any other supported types to make sure this is covered.

[krisbiradar]

Hi @roji i can take this up.

[roji]

@krisbiradar great, but please post a clear plan of the findings and changes before submitting a PR.

[krisbiradar]

Sure

[krisbiradar]

Hi, @roji — I think I figured it out.

If we look at the JsonTimeOnlyReaderWriter:

// JsonTimeOnlyReaderWriter.cs
writer.WriteStringValue(value.ToString("o", CultureInfo.InvariantCulture));

we can see that TimeOnly is converted to string using the round-trip format specifier ("o"),
so it preserves full precision irrespective of whether or not it has any milliseconds.

Whereas in the StringTimeOnlyConverter:

// StringTimeOnlyConverter.cs
protected static new Expression<Func<TimeOnly, string>> ToString()
    => v => v.Ticks % 10000000 == 0
        ? string.Format(CultureInfo.InvariantCulture, @"{0:HH\:mm\:ss}", v)
        : v.ToString("o");

precision is preserved only if the ticks are not divisible by 10,000,000 (i.e., when there are fractional seconds).

[roji]

Right, that's pretty much what I wrote in the original OP (note also that this is only about TimeOnly, but we're also aware of a problem with byte array).

[krisbiradar]

Got you , will check the byte array one and submit a pr containing the fixes and tests so as to validate this issue going further.

[krisbiradar]

I dug a little deeper here and wanted to share my findings regarding how EF Core handles serialization for JSON columns, specifically for byte[] and TimeOnly types when using the SQLite provider.

---

byte[]

• For byte[], I confirmed that SqliteJsonByteArrayReaderWriter is being correctly passed as the jsonValueReaderWriter.
• This seems correct, as it implements the provider-specific logic (hex encoding/decoding) expected by SQLite.
• Conclusion: The mapping for byte[] appears to be set up correctly.

TimeOnly

• For TimeOnly, however, the default JsonTimeOnlyReaderWriter is used.
• This implementation does not account for milliseconds when serializing/deserializing, which could potentially lead to subtle data loss or comparison mismatches.
• Conclusion: There may be an opportunity to improve or override the default TimeOnly handling for SQLite, especially if milliseconds precision is important.

looks like the byte[] issue is because of something or i might be missing something

thoughts ?

[roji]

@krisbiradar I don't really have time to spend time on this issue right now (if I spend time investigating it I might as well just fix it). What I do know that is that we have a failing test - ByteArrayTypeTest.Query_property_within_json - which indicates a problem that needs to be fixed.

[krisbiradar]

I found the issue with byte [] as well , the issue is despite having SqliteJsonByteArrayReaderWriter only the ToJsonTyped Method is getting called which correctly saves the values in the database but while reading it doesnt call FromJsonTyped due to which the byte array now has more elements that we actually saved and that too numerical ascii value of the hex string characters

will go deeper in this and come up with a solution as well as a PR

[krisbiradar]

So upon further investigation i found out the FromJsonTyped is indeed getting called when we try to query the data with non json column filter or with no filter at all and when we select a full entity. The same doesn't work when we filter data with a json column check. i tried passing a global converter to the SqliteByteArrayTypeMapping that converted byte array to hex and back which seemed to pass the failing test you mentioned but it led to an issue that even non json columns were using it so now i am onto finding the correct spot to put the converter into which i should be done with in a couple of days
please correct if i am wrong here.

[martijnremmen]

I'm running into this issue when querying on a TimeOnly column where the TimeOnly value is in a list .Where(x => y.Contains(x.SomeTime))

When looking at the DbCommand debug it seems to convert the TimeOnly[] to [Parameters=[@__filters_SomeTimeOnlyFilter_0='["15:00:00.0000000"]'. This ofcourse doesn't match with the Sqlite Text column with value "15:00:00". So I don't get any results, when swapping to Sql Server this works fine.