`ExecuteUpdate` doesn't work on JSON columns?

[aradalvand]

I couldn't find an existing for this, let me know if there is one.

I was surprised to find out that ExecuteUpdate doesn't seem to work at all on the new JSON columns. It doesn't work on individual properties on the JSON document, nor does it work on the entire column (which should've been straightforward to implement), nor does it work on JSON collections. Is this expected?!

Repro:

Program.cs:

using Microsoft.EntityFrameworkCore;

using var db = new AppDbContext();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

Console.WriteLine("------------------ Insert:");

var product = new Product
{
    Title = "Foo",
    MainTrait = new()
    {
        Title = "main-trait",
        Description = "main-trait-des"
    },
    Traits = [
        new()
        {
            Title = "first-trait",
            Description = "first-trait-des"
        },
        new()
        {
            Title = "second-trait",
            Description = "second-trait-des"
        },
    ]
};
db.Add(product);
db.SaveChanges();

Console.WriteLine("------------------ Update:");
List<Trait> newTraits = [
    new()
    {
        Title = "other-trait",
        Description = "other-trait-des"
    }
];
db.Products.ExecuteUpdate(b => b.SetProperty(p => p.MainTrait, newTraits[0])); // THROWS
db.Products.ExecuteUpdate(b => b.SetProperty(p => p.MainTrait.Title, "test")); // THROWS
db.Products.ExecuteUpdate(b => b.SetProperty(p => p.Traits, newTraits)); // THROWS

class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
        optionsBuilder
            .UseSqlServer(@"Database=JsonTest;User ID=sa;Password=YOUR_PASSWORD;TrustServerCertificate=true")
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .OwnsOne(p => p.MainTrait, b => b.ToJson())
            .OwnsMany(p => p.Traits, b => b.ToJson());
    }

    public DbSet<Product> Products => Set<Product>();
}

class Product
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public required Trait MainTrait { get; set; }
    public required List<Trait> Traits { get; set; }
}

class Trait
{
    public required string Title { get; set; }
    public required string Description { get; set; }
}

The .csproj file:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <RootNamespace>efcore_json_test</RootNamespace>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" />
  </ItemGroup>

</Project>

The exception details:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Product>()
    .Select(p => IncludeExpression(
        EntityExpression:
        IncludeExpression(
            EntityExpression:
            p,
            NavigationExpression:
            EF.Property<Trait>(p, "MainTrait"), MainTrait)
        ,
        NavigationExpression:
        MaterializeCollectionNavigation(
            Navigation: Product.Traits,
            subquery: EF.Property<List<Trait>>(p, "Traits")
                .AsQueryable()), Traits)
    )
    .ExecuteUpdate(b => b.SetProperty<Trait>(
        propertyExpression: p => p.MainTrait,
        valueExpression: __get_Item_0))' could not be translated. Additional information: The following lambda argument to 'SetProperty' does not represent a valid property to be set: 'p => p.MainTrait'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.ExecuteUpdate[TSource](IQueryable`1 source, Expression`1 setPropertyCalls)
   at Program.<Main>$(String[] args) in /home/arad/scratchpad/efcore-json-test/Program.cs:line 41

[ajcvickers]

Duplicate of #28766 and #32058

[aradalvand]

The workaround for #32058 mentioned in this comment doesn't actually work for JSON columns, because of #28766, but then even if #28766 was implemented, that alone would still not solve the problem because it wouldn't work for collections (e.g. the Product.Traits in the example above).

So, am I right in thinking that this means #32058 isn't just a nice-to-have, but pretty crucial?

Currently, there seems to be no actual workaround for this, ExecuteUpdate simply doesn't work on JSON columns. Right? Does that mean we have to resort to traditional change-tracking-style updates for JSON columns for now?

[ajcvickers]

@aradalvand Correct, the bulk update APIs currently do not support updating JSON columns.