ExecuteUpdate with OwnsOne property in target table fails in efcore 7.0.12

[anoordover]

See my repository https://github.com/anoordover/OracleDbDemo

The methods ExecuteUpdateWithSelectNew1 en ExecuteUpdateWithSelectNew3 fail

The method ExecuteUpdateWithSelectNew2 succeeds

[ajcvickers]

@anoordover ExecuteUpdate can only be used to update columns in a single table. So something like this cannot be expected to work:

var r = db.Credits.Where(c => c.Id == 1)
    .Select(c => new
    {
        credit = c,
        declaration = db.Declarations
            .First(d => d.Reference == c.DeclarationReference)
    })
    .ExecuteUpdate(calls => calls.SetProperty(
        c => c.credit.DeclarationId,
        c => c.declaration.Id));

Because declaration comes from the Declarations table, while credit comes from the Credits table.

[anoordover]

You can use ExecuteUpdate to update columns in one table with a column fetch from another table.
See the update on the Declaration table where the update succeeds because there is no OwnsOne property in de target table.
@roji adviced me to try using 8.0 rc2 of ef core. Because I was using Oracle which doesn't have a 8.0 implementation available I created two branches doing the same tests using NpgSql on 8.0 rc2.

• https://github.com/anoordover/OracleDbDemo/tree/feature/npgsql_8.0rc2_OwnsOne
  • There is a work-around for updating the table with the OwnsOne property by selecting only the fields being used to a new'ed up Credit. Other updates on the table containing the OwnsOne property fail.
• https://github.com/anoordover/OracleDbDemo/tree/feature/npgsql_8.0rc2_ComplexProperty
  • all updates pass

When you have a work-station that has docker available you can run the tests yourself. I use a testcontainer (https://dotnet.testcontainers.org/modules/postgres/) to run the code (the main branch uses an Oracle testcontainer).

[ajcvickers]

@roji I think you're going to need to investigate here eventualy. There are five different scenarios. There are different failures for some on SQL Server and Oracle, and the same failures for others. Also, some scenarios start failing on SQL Server after #30528 was merged, which implies that #30528 has regressed something, but I'm not sure if those queries were supposed to work in the first place.

[roji]

Sure thing. I'm adding this to my investigation list for when I have a bit more time - but realistically only after 8.0 is released I think...

[roji]

@anoordover thanks for your patience and sorry it took so long to get around to this.

There's a lot going on above - it's always a good idea to submit a short, minimal console program that demonstrates a single failing scenario (and ideally on a provider we maintain, as you've indeed done for PostgreSQL above). That can help cut down the time needed to investigate etc.

In any case, I've confirmed a failure that's reproducible with the following minimal console program:

ctx.Credits.Where(c => c.Id == 1)
    .Join(ctx.Declarations,
        c => c.DeclarationReference,
        d => d.Reference,
        (credit, declaration) => new {credit, declaration})
    .ExecuteUpdate(calls => calls.SetProperty(
        c => c.credit.DeclarationId,
        c => c.declaration.Id));

Full minimal repro

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

ctx.Credits.Where(c => c.Id == 1)
    .Join(ctx.Declarations,
        c => c.DeclarationReference,
        d => d.Reference,
        (credit, declaration) => new {credit, declaration})
    .ExecuteUpdate(calls => calls.SetProperty(
        c => c.credit.DeclarationId,
        c => c.declaration.Id));

public class BlogContext : DbContext
{
    public virtual DbSet<Credit> Credits { get; set; }
    public virtual DbSet<Declaration> Declarations { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<Credit>().OwnsOne(c => c.Period);
}

public class Credit
{
    public long Id { get; set; }
    public string Reference { get; set; }
    public string DeclarationReference { get; set; }
    public Period Period { get; set; }
    public long? DeclarationId { get; set; }
    public Declaration? Declaration { get; set; }
}

public class Period
{
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

public class Declaration
{
    public long Id { get; set; }
    public string Reference { get; set; }
}

The cause is essentially the same as #30528 - the fact that entity has an owned entity causes nav expansion to inject an IncludeExpression into the query tree, which causes ExecuteUpdate to fail (and also is meaningless, since ExecuteUpdate doesn't query for anything). The fix in #30571 was to prune out the IncludeExpression, but this was done only when its found at the top of the shaper tree; but because of the join in the query above, the shaper contains an anonymous object with the IncludeExpression nested inside. The fix here is to visit recursively, pruning IncludeExpressions everywhere, and not only at the top.

Note that complex properties don't cause IncludeExpressions to get injected, which is why there's no bug there.

@anoordover if there are other problems you wanted to raise here, please open separate issues with minimal repros for those - I'll use this issue to track the above only.