[SQLite] Allow ExecuteUpdate updating of JSON properties to arbitrary relational expressions
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
#28766 added support for partial updating of JSON properties via ExecuteUpdate. One thing that isn't yet supported is updating such properties to arbitrary relational expressions:
```c#
_ = await ExecuteUpdateAsync(b => b.SetProperty(x => x.JsonColumn.Foo, x => x.JsonColumn.Bar + j.JsonColumn.Baz)
```
This also includes setting the JSON property to a regular, non-JSON colum:
```c#
_ = await ExecuteUpdateAsync(b => b.SetProperty(x => x.JsonColumn.Foo, x => x.Bar)
```
The reason for this limitation is that the database functions which perform partial JSON updates (e.g. JSON_MODIFY/modify on SQL Server) accept JSON types, i.e. string/int/bool; all other relational types, need to be converted to their JSON representation. While we can do that client-side for simple constants and parameters (via JsonValueReaderWriter), and can even support setting a JSON property to another JSON property (the JSON string representation can simply be copied), for anything else we need to convert the relational value to the correct JSON representation **server-side**.
On SQL Server, this can be done with [JSON_OBJECT()](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-object-transact-sql?view=sql-server-ver17) as follows (implemented in #36730):
```sql
SELECT JSON_VALUE(JSON_OBJECT('v': CAST('2020-01-01 12:00' AS datetime2)), '$.v')
```
However, this is highly database-specific and support needs to be investigated etc.
Note that since string/int/bool are supported by database partial update functions, arbitrary expression setting does work for them.
For test coverage, see StoreTypeRelationalTestBase.TestExecuteUpdateWithinJsonToNonJsonColumn.
Contributor guide
Assessment
This issue has not been assessed yet.