Allow Json property to be updated by using ExecuteUpdate or as a single property

[zulander1]

I am using a JSON column. The issue is that I need to update a JSON column without retrieving the full dataset. Obviously, updating multiple sets in one execution is ideal. I know this wasn’t planned a long time ago (#32367). I am using NET 9

A potential solution is to use ExecuteUpdate:

Ex.: context.Orders.Where(F => F.Id == 100000).ExecuteUpdate(f => f.SetProperty(x => x.Agent, new Employee() { EmployeeCode = "123" }));

Or to allow Property to support complex types

foreach (var item in Enumerable.Range(1, 3))
{
	Order order = new() { Agent = new Employee() { EmployeeCode = $"123" }, Id = item };

	context.Attach(order);
	context.Entry(order).Property(nameof(Order.Agent)).IsModified = true;
}

context.SaveChanges();

[ajcvickers]

@zulander1 ExecuteUpdate can already be used with complex types. This means that when #31252 is implemented, it should also be possible to use complex types mapped to JSON with ExecuteUpdate.

Likewise, it is already possible to mark parts of a complex type as Modified. You need to use the ComplexProperty method instead of Property. For example:

context.Entry(order).ComplexProperty(nameof(Order.Agent)).IsModified = true;

[zulander1]

It is wired, I am getting "'The complex property 'Order.Agent' could not be found. Ensure that the property exists and has been included in the model as a complex property.'"

foreach (var item in Enumerable.Range(1, 3))
{
	Order order = new() { Agent = new Employee() { EmployeeCode = $"123" }, Id = item };

	context.Attach(order);
	context.Entry(order).ComplexProperty(nameof(Order.Agent)).IsModified = true;
}

context.SaveChanges();

When i am executing this:

context.Orders.Where(f => f.Id == 1).ExecuteUpdateAsync(f => f.SetProperty(d => d.Agent, g => new Employee() { EmployeeCode = $"123" }));
context.Orders.Where(f => f.Id == 1).ExecuteUpdateAsync(f => f.SetProperty(d => d.Agent.EmployeeCode, g => "456"));

i am getting: "System.InvalidOperationException: 'The LINQ expression 'DbSet()
.Where(o0 => o0.Id == 1)
.Select(o0 => Include(
Entity: o0,
Navigation: Agent, EF.Property(o0, "Agent"))
.ExecuteUpdate(f => f.SetProperty(
propertyExpression: d => d.Agent,
valueExpression: g => new Employee{ EmployeeCode = "123" }
))' could not be translated. Additional information: The following lambda argument to 'SetProperty' does not represent a valid property to be set: 'd => d.Agent'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
"

public partial class DbSavaContext : DbContext
{
    public DbSavaContext()
    {
    }

    public DbSavaContext(DbContextOptions<DbSavaContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__tOrder__3214EC07D06CC76A");

            entity.ToTable("tOrder");

            entity.OwnsOne(p => p.Agent, builder => { builder.ToJson(); });
        });
    }
}

public class Employee
{
    public string EmployeeCode { get; set; } = null!;

    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
}

public partial class Order
{
    public int Id { get; set; }

    public virtual Employee? Agent { get; set; }
}

[ajcvickers]

@zulander1 This is because you are using "owned entity types", rather than complex types. Currently the only way to map to JSON is through owned types, but this support has fundamental limitations, and so we are working on complex types to better support JSON. This is tracked by #31252.

[zulander1]

Great, thanks, is there any workaround for this ? Moving to Complex type only support required fields

[roji]

Duplicate of #28766

[roji]

Yeah, as @ajcvickers the best solution here would be to have JSON mapping via complex types; even when that's done, having ExecuteUpdate work inside JSON documents will require some extra work - that's covered by #28766.

In the meantime, you can use SQL as a workaround. If you're OK with loading the entire JSON document, then you can use regular SaveChanges - EF already supports partial updating of JSON documents (when mapped as owned entities).

[ajcvickers]

EF already supports partial updating of JSON documents (when mapped as owned entities

For example, to mark all properties of an owned entity as modified:

context.Entry(order)
    .Reference(nameof(Order.Agent))
    .IsModified = true;

Or just a single property:

context.Entry(order)
    .Reference(nameof(Order.Agent))
    .TargetEntry!
    .Property(nameof(Employee.EmployeeCode))
    .IsModified = true;