Json column has different support for Chinese in addition and editing

[TimChen44]

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseCollation("Chinese_PRC_CI_AS");

    modelBuilder.Entity<User>(entity =>
    {
        entity.Property(e => e.UserId).ValueGeneratedNever();
    });

    modelBuilder.Entity<User>().OwnsOne(u=>u.Address , u =>
    {
        u.ToJson();
    });
}

public partial class User
{
    [Key]
    public Guid UserId { get; set; }
    [StringLength(50)]
    public string Name { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string City { get; set; }
    public User User { get; set; }
}

When data is added, the Chinese in Json column is escaped.

User user = new User()
{
    UserId = Guid.NewGuid(),
    Name = "超超",
    Address = new Address()
    {
        City = "上海",
    }
};
context.Add(user);
context.SaveChanges();

If you edit this record, the Chinese in the Json column will not be escaped.

var user = context.User.First();
user.Address.City = "北京";
context.SaveChanges();

Whether there is a parameter configuration that allows us to define whether Chinese is escaped.

Include provider and version information

EF Core version:7.02
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0)
Operating system:Windows 11
IDE: Visual Studio 2022 17.5.0 Preview 6.0

[roji]

This is probably a result of us handling partial updates differently than full-document updates... /cc @maumar

[maumar]

we populate parameter value using the same code paths, and they are both in the same form in the parameter string:

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [User] ([Address], [UserId], [Name])
VALUES (@p0, @p1, @p2);
',N'@p0 nvarchar(23),@p1 uniqueidentifier,@p2 nvarchar(50)',@p0=N'{"City":"\u4E0A\u6D77"}',@p1='F41F2D67-0FDB-4C09-8681-A1180CB2920C',@p2=N'超超'

vs

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [User] SET [Address] = JSON_MODIFY([Address], ''strict $.City'', JSON_VALUE(@p0, ''$[0]''))
OUTPUT 1
WHERE [UserId] = @p1;
',N'@p0 nvarchar(16),@p1 uniqueidentifier',@p0=N'["\u5317\u4EAC"]',@p1='F41F2D67-0FDB-4C09-8681-A1180CB2920C'

however, the output is different between JSON_VALUE and JSON_QUERY:

SELECT JSON_VALUE(N'{"City" : "\u5317\u4EAC"}', '$.City')

returns 北京

whereas

SELECT JSON_QUERY(N'{"City" : "\u5317\u4EAC"}')

returns {"City" : "\u5317\u4EAC"}

[maumar]

to get consistent (escaped) behavior we can escape the escape char when generating string for single property value.

SELECT JSON_VALUE(N'{"City" : "\\u5317\\u4EAC"}', '$.City')

returns \u5317\u4EAC

We need provider specific ModificationCommand to do that properly, but we already need provider specific ModificationCommand for JSON on Sqlite work.

[ajcvickers]

Note from triage: Try including Unicode characters directly in the string, rather than with escape sequences.

[maumar]

we still save data in the escaped form. We now use Utf8JsonWriter.WriteStringValue which does the escaping