EF Core 11 relational regression: `ExecuteUpdate` prunes its update-target join

[indrajitjadeja]

Bug description

Summary

Seven Parent rows reference seven of 20 Child rows through a required one-to-one relationship. ExecuteUpdate on the parent query should change only those seven children. With Microsoft.EntityFrameworkCore.SqlServer 10.0.11, it does. With 11.0.0-rc.1.26425.128, the generated SQL drops the joined Child update target from FROM but still says UPDATE [c], causing SqlException 208: Invalid object name 'c' and changing no rows. The model, data, query, and database were unchanged between runs. This points to EF Core 11 relational update-target join pruning as the possible root cause; SQL Server's invalid target alias is the observed symptom.

Possible root cause: relational join pruning

EF Core 11 may mark the required one-to-one inner join IsPrunable = true: joining each parent to its required child does not remove parent rows. But this ExecuteUpdate targets the joined child, and 13 children have no parent. When EF Core's relational SqlTreePruner visits setter values and then prunes the select, the setter's target column does not protect the joined child table; the prunable join disappears. SQL Server's SQL generator still writes UPDATE [c] from the target alias, while its FROM is built from the now-pruned select.

Preserve the join required to identify update-target rows, or reject the update before execution. Merely requiring a WHERE is not the fix: the correct EF10 SQL uses an INNER JOIN without a WHERE.

Reproduction environment

• Database: SQL Server version 15.0.4382.1
• .NET SDK: 10.0.400 for the EF Core 10.0.11 run; 11.0.100-preview.7.26381.103 for the EF Core 11.0.0-rc.1.26425.128 run.

Your code

## Reproduce

Create a console project targeting `net10.0` with NuGet package `Microsoft.EntityFrameworkCore.SqlServer` version `10.0.11`. Repeat with `net11.0` and package version `11.0.0-rc.1.26425.128`. Use the same code for both runs.

**Entities:** The parent holds a required FK to its child. Children without a parent must remain unchanged.


public sealed class RootEntity
{
    public int Id { get; set; }
    public int RequiredAssociateId { get; set; }
    public AssociateType RequiredAssociate { get; set; } = null!;
}

public sealed class AssociateType
{
    public int Id { get; set; }
    public string String { get; set; } = null!;
    public int Int { get; set; }
}


**Context:** Separate physical `Parent` and `Child` tables. The FK is required and unique because the relationship is one-to-one.


public sealed class ReproContext(DbContextOptions<ReproContext> options) : DbContext(options)
{
    public DbSet<RootEntity> Roots => Set<RootEntity>();
    public DbSet<AssociateType> Associates => Set<AssociateType>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AssociateType>(b =>
        {
            b.ToTable("Child", "dbo");
            b.HasKey(c => c.Id);
            b.Property(c => c.Id).ValueGeneratedNever();
        });
        modelBuilder.Entity<RootEntity>(b =>
        {
            b.ToTable("Parent", "dbo");
            b.HasKey(r => r.Id);
            b.Property(r => r.Id).ValueGeneratedNever();
            b.HasOne(r => r.RequiredAssociate).WithOne()
                .HasForeignKey<RootEntity>(r => r.RequiredAssociateId)
                .IsRequired().OnDelete(DeleteBehavior.NoAction);
        });
    }
}


**Setup:** Use EF Core APIs to recreate the database and insert **20 children**, but only **seven parents**; the 13 unreferenced children are essential to see an overly broad update. 


const string connectionString = @"<connection string for SQL Server database>";
var options = new DbContextOptionsBuilder<ReproContext>()
    .UseSqlServer(connectionString).Options;
using var context = new ReproContext(options);
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
try
{
    var children = Enumerable.Range(1, 20)
        .Select(id => new AssociateType { Id = id, String = "foo", Int = 1 })
        .ToArray();
    context.Associates.AddRange(children);
    context.Roots.AddRange(Enumerable.Range(1, 7)
        .Select(id => new RootEntity
        {
            Id = id,
            RequiredAssociateId = id,
            RequiredAssociate = children[id - 1]
        }));
    context.SaveChanges();

}
finally
{
    context.Database.EnsureDeleted();
}


**Trigger:** Query **parents**, but update columns on the referenced **children**. A correct implementation updates exactly seven rows and leaves children 8–20 untouched.


var affected = context.Roots.ExecuteUpdate(s => s
    .SetProperty(r => r.RequiredAssociate.String, "foo_updated")
    .SetProperty(r => r.RequiredAssociate.Int, 20));

var updated = context.Associates.Count(c => c.String == "foo_updated" && c.Int == 20);
var unreferencedUpdated = context.Associates.Count(c =>
    c.Id > 7 && c.String == "foo_updated" && c.Int == 20);
// Expected: affected == 7, updated == 7, unreferencedUpdated == 0.

Stack traces

Update threw: SqlException 208: Invalid object name 'c'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'c'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.Connection.SqlConnectionInternal.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean isRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& executeTask, Boolean& usedCache, Boolean asyncWrite, Boolean isRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean isRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.<>c.<NonQueryResult>b__30_0(DbContext _, ValueTuple`3 state)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.NonQueryResult(RelationalQueryContext relationalQueryContext, RelationalCommandResolver relationalCommandResolver, Type contextType, CommandSource commandSource, Boolean threadSafetyChecksEnabled)
   at lambda_method70(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteUpdate[TSource](IQueryable`1 source, Action`1 setPropertyCalls)
   at Program.Main() in Program.cs:line 37

Verbose output

## Generated SQL and observed results

**EF Core 10.0.11 — expected behavior, 7 rows updated:**


UPDATE [c]
SET [c].[String] = @p,
    [c].[Int] = @p1
FROM [dbo].[Parent] AS [p]
INNER JOIN [dbo].[Child] AS [c] ON [p].[RequiredAssociateId] = [c].[Id]


The join selects only children referenced by parents. Result: affected **7**, updated **7**, unreferenced updated **0**. There is no `WHERE` clause because the join supplies the restriction.

**EF Core 11.0.0-rc.1.26425.128 — actual broken SQL:**


SET NOCOUNT OFF;
UPDATE [c]
SET [c].[String] = @p,
    [c].[Int] = @p1
FROM [dbo].[Parent] AS [p]


`[c]` is still the update target but no longer appears in `FROM`. The database raises **SqlException 208: `Invalid object name 'c'`**; the update returns no count and changes **0** rows. The **expected EF11 SQL** is the same `UPDATE ... FROM Parent INNER JOIN Child ON Parent.RequiredAssociateId = Child.Id` shape shown above for EF10 (the additional `SET NOCOUNT OFF` is incidental).

EF Core version

11.0.0-rc.1.26425.128

Database provider

Microsoft.EntityFrameworkCore.Relational

Target framework

.NET 11

Operating system

Windows 11

IDE

No response

[MihuBot]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

• Improve pruning to properly support ExecuteUpdate #31407