dotnet / dotnet/efcore

[SQLite] Allow ExecuteUpdate updating of JSON properties to arbitrary relational expressions

Open
#36,688 1 comment 0 reactions 0 assignees View on GitHub
area-bulkupdate area-json area-sqlite
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.