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

[GZEI]

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:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

public class IssueContext : DbContext
{
    public DbSet<TestObj> As { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"OMITTED");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestObj>().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

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)

[maumar]

Switching to lax would be too breaking, especially when collaborating with earlier versions. But making the setting configurable on sql server seems like a decent option.

[roji]

Note: an improvement on the SQL Server side may be coming (i.e. an API that fully distinguishes between modifying and removing a key).

[roji]

My recommendation here would be for the user to do string manipulation in a command interceptor to replace strict by lax, as we wait for a better solution in SQL Server itself - at least until we see this being a problem for more users.

[Marcel0024]

dupe of #32301

@roji It was definitely hit by more people but filed under different names. Saw another one but can't find it anymore

[jmrtnz94]

I ran into this problem this week.

In my json column I'm storing an Array of Bulletpoints. Current version only has "Text" property. I'm now adding a new property called "Order".

I have a scheduled task that refreshes my data every so often, which is now responsible to add the new "Order" property values to that document. When that runs it fails on certain records but for other records it adds the new "Order" property.

It's interesting because I'm only running into this error on certain rows in my table. The only thing I've noticed is the SQL that gets generated. What I don't understand is what cause EF Core to switch between the 2 queries. Should be using the same SQL.

Here is a snippet of the working SQL.

SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [Table] SET [Bullets] = @p0,
OUTPUT INSERTED.[ValidFrom], INSERTED.[ValidTo]

Here is a snippet of the non working SQL.

SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [Table] SET [Bullets] = JSON_MODIFY([Bullets], 'strict $[0].Order', @p0)
OUTPUT INSERTED.[ValidFrom], INSERTED.[ValidTo]

[KuroroSam]

I ran into this problem this week.

In my json column I'm storing an Array of Bulletpoints. Current version only has "Text" property. I'm now adding a new property called "Order".

I have a scheduled task that refreshes my data every so often, which is now responsible to add the new "Order" property values to that document. When that runs it fails on certain records but for other records it adds the new "Order" property.

It's interesting because I'm only running into this error on certain rows in my table. The only thing I've noticed is the SQL that gets generated. What I don't understand is what cause EF Core to switch between the 2 queries. Should be using the same SQL.

Here is a snippet of the working SQL.

SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; UPDATE [Table] SET [Bullets] = @p0, OUTPUT INSERTED.[ValidFrom], INSERTED.[ValidTo] Here is a snippet of the non working SQL.

SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; UPDATE [Table] SET [Bullets] = JSON_MODIFY([Bullets], 'strict $[0].Order', @p0) OUTPUT INSERTED.[ValidFrom], INSERTED.[ValidTo]

The first query is generated by setting a new Bullets object directly: var bullets = new Bullets(); Table.Bullets = bullets

The second, problematic query is generated when iterating over existing items in table.Bullets and updating each Order property: foreach (var bullet in table.Bullets) { bullet.Order = order; }

As you mention, the first one is working by avoiding the strict mode.