dotnet / dotnet/efcore

Working with JSON columns in SQLServer uses strict mode, could result in "SqlException: Property cannot be found on the specified JSON path" exception.

Open
#33,565 7 comments 7 reactions 0 assignees View on GitHub
area-json customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Hello,

the SqlServerUpdateSqlGenerator.cs forces the usage of strict mode when working with JSON columns in SQL Server.
This leads to SQL errors when updating values that do not exist within the JSON path.
As a consequence, JSON columns where a key does not exist (null values seem to be removed by the SQLServer when using lax mode) cannot be directly updated by EF again.
This can occur if multiple applications modify the JSON column (e.g. by using lax mode) or when working with already existing data.
Removing strict / using lax mode in SqlServerUpdateSqlGenerator.cs fixes the problem.

As a compromise, making the usage of strict mode configurable/optional would also be a solution.

### Include your code

Model:

```C#
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

public class IssueContext : DbContext
{
public DbSet As { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"OMITTED");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().OwnsOne(x => x.ComplexObjectParent, builder =>
{
builder.ToJson();
builder.OwnsOne(y => y.ComplexObjectChild1);
builder.OwnsOne(y => y.ComplexObjectChild2);
});
}
}

public class TestObj
{
[Key]
public int Id { get; set; }
public string? Test { get; set; }
public ComplexObjectParent? ComplexObjectParent { get; set; }
}

public class ComplexObjectParent
{
public ComplexObjectChild1? ComplexObjectChild1 { get; set; }
public ComplexObjectChild2? ComplexObjectChild2 { get; set; }

}

public class ComplexObjectChild1
{
public string? Test { get; set; }
}
public class ComplexObjectChild2
{
public string? Test { get; set; }
}
```

Program.cs
```C#
using Microsoft.EntityFrameworkCore;
var dbContext = new IssueContext();
dbContext.As.Add(new TestObj()
{
Test = "TEST",
ComplexObjectParent = new ComplexObjectParent()
{
ComplexObjectChild2 = new ComplexObjectChild2() { Test = "A" }
}
});
dbContext.SaveChanges();
dbContext.Database.ExecuteSql($"UPDATE [As] SET ComplexObjectParent = JSON_MODIFY(ComplexObjectParent , '$.ComplexObjectChild1', null);"); // To simulate externally modified JSON data removing the ComplexObjectChild1 key.
var updateTest = dbContext.As.First();
updateTest.ComplexObjectParent.ComplexObjectChild1 = new ComplexObjectChild1 { Test = "B" };
dbContext.SaveChanges();

```

### Include stack traces

Relevant exception:

```
SqlException: Property cannot be found on the specified JSON path.
```

### Include verbose output

-

### Include provider and version information

EF Core version: 8.0.4
Database provider: (Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (.NET 8.0)

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.