Json: updating property with conversion from string to other type fails on sql server

[maumar]

when updating a "normal" (non-string) property, we produce the following sql:

@p0='[true]' (Nullable = false) (Size = 6)
@p1='[false]' (Nullable = false) (Size = 7)
@p2='1'

SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [JsonEntitiesAllTypes] SET [Collection] = JSON_MODIFY([Collection], 'strict $[0].TestBoolean', CAST(JSON_VALUE(@p0, '$[0]') AS bit)), [Reference] = JSON_MODIFY([Reference], 'strict $.TestBoolean', CAST(JSON_VALUE(@p1, '$[0]') AS bit))
OUTPUT 1
WHERE [Id] = @p2;

however if we update property that is string on client side but (say) bool on the server we issue the following:

@p0='[false]' (Nullable = false) (Size = 7)
@p1='1'

SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [JsonEntitiesConverters] SET [Reference] = JSON_MODIFY([Reference], ''strict $.StringTrueFalseConvertedToBool'', JSON_VALUE(@p0, ''$[0]''))
OUTPUT 1
WHERE [Id] = @p1;

i.e. we don't wrap the value in cast to a provider type, which then results in us storing it as string and causes problems when querying the values. We should look if the property has a converter and check provider type when determining if we need CAST or not.

[maumar]

reopening for servicing given the #30598 customer report (relatively common scenario)