ExecuteUpdate does not report affected rows if empty entity is created on the same open connection

[frederikrosenberg]

Bug description

When using ExecuteUpdate with a provided open SqlConnection and creating an empty entity (an entity with only the id property with auto increment) before calling ExecuteUpdate the result of ExecuteUpdate is -1

Just to summarize, the following is required for the bug:

• A DbContext created with an open SqlConnection
• Creation of an entity with no properties being set before calling ExecuteUpdate

Notice in the trace.sql in the gist that the insert for the empty entity is not wrapped in sp_executesql. This causes the SET NOCOUNT ON; to affected the ExecuteUpdate query. If the insert was wrapped in sp_executesql this would not be a problem, like the insert for the more entity with the name field.

Your code

https://gist.github.com/frederikrosenberg/74362389151aec5a4e54e7b1376be390

EF Core version

9.0.10

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

NET 9

Operating system

Windows 11

IDE

Rider 2025.2

[roji]

Sorry, but I don't think I understand what you're reporting exactly. I condensed your gist into the simple, single-file console program below. The program runs successfully, outputting "Number of affected rows: 1, expected: 1". The SQL generated from logging is the following:

info: 11/3/2025 12:57:34.452 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (23ms) [Parameters=[@p0='Test1' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [EntityWithMoreColumns] ([Name])
      OUTPUT INSERTED.[Id]
      VALUES (@p0);
info: 11/3/2025 12:57:34.474 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [EntityWithOnlyId]
      OUTPUT INSERTED.[Id]
      DEFAULT VALUES;
info: 11/3/2025 12:57:34.558 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (6ms) [Parameters=[@__name_0='ea9cdd62-abb7-46c2-9776-458cd0db28cf' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE [e]
      SET [e].[Name] = @__name_0
      FROM [EntityWithMoreColumns] AS [e]

So everything seems to be working as expected. Please submit a similar minimal console program that shows the problem, or tweak the below code sample to do the same.

Attempted repro

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

// Test data for updating
context.EntityWithMoreColumns.Add(new EntityWithMoreColumns { Name = "Test1" });
await context.SaveChangesAsync();

// Removing these lines gives correct affected rows count
context.EntityWithOnlyId.Add(new EntityWithOnlyId());
await context.SaveChangesAsync();

var name = Guid.NewGuid().ToString();

var affected = await context.EntityWithMoreColumns.ExecuteUpdateAsync(x => x.SetProperty(c => c.Name, name));
Console.WriteLine($"Number of affected rows: {affected}, expected: 1");

public class BlogContext : DbContext
{
    public DbSet<EntityWithOnlyId> EntityWithOnlyId { get; set; }
    public DbSet<EntityWithMoreColumns> EntityWithMoreColumns { 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();
}

public class EntityWithMoreColumns
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class EntityWithOnlyId
{
    public int Id { get; set; }
}

[frederikrosenberg]

Thanks for the response.

Your example is missing one thing: the existing open sql connection. The DbContext have to be created using an open SqlConnection before the bug shows.

See line 21-24 in the provided code.

Updated repro

await using var sqlConnection = new SqlConnection("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false");
await sqlConnection.OpenAsync();

await using var context = new BlogContext(new DbContextOptionsBuilder<BlogContext>().UseSqlServer(sqlConnection).Options);

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

// Test data for updating
context.EntityWithMoreColumns.Add(new EntityWithMoreColumns { Name = "Test1" });
await context.SaveChangesAsync();

// Removing these lines gives correct affected rows count
context.EntityWithOnlyId.Add(new EntityWithOnlyId());
await context.SaveChangesAsync();

var name = Guid.NewGuid().ToString();

var affected = await context.EntityWithMoreColumns.ExecuteUpdateAsync(x => x.SetProperty(c => c.Name, name));
Console.WriteLine($"Number of affected rows: {affected}, expected: 1");

public class BlogContext : DbContext
{
    public DbSet<EntityWithOnlyId> EntityWithOnlyId { get; set; }
    public DbSet<EntityWithMoreColumns> EntityWithMoreColumns { get; set; }
    
    public BlogContext(DbContextOptions<BlogContext> options) : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class EntityWithMoreColumns
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class EntityWithOnlyId
{
    public int Id { get; set; }
}

[roji]

Thanks - I can see it now (corrected your repro as the connection can't be used to drop the database)

In the update pipeline, we do SET NOCOUNT ON as an optimization; when ExecuteUpdate is executed afterwards on the same connection, this leaks and causes ExecuteUpdate to not return the row count correctly.

We should probably prevent this by prepending SET NOCOUNT OFF before ExecuteUpdate. @cincuranet feel like looking at this?

Confirmed minimal repro

const string connectionString = "Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false";

using (var setupContext = new BlogContext(new DbContextOptionsBuilder<BlogContext>().UseSqlServer(connectionString).Options))
{
    await setupContext.Database.EnsureDeletedAsync();
    await setupContext.Database.EnsureCreatedAsync();
}

await using var sqlConnection = new SqlConnection(connectionString);
await sqlConnection.OpenAsync();

await using var context = new BlogContext(new DbContextOptionsBuilder<BlogContext>().UseSqlServer(sqlConnection).Options);

// Test data for updating
context.EntityWithMoreColumns.Add(new EntityWithMoreColumns { Name = "Test1" });
await context.SaveChangesAsync();

// Removing these lines gives correct affected rows count
context.EntityWithOnlyId.Add(new EntityWithOnlyId());
await context.SaveChangesAsync();

var name = Guid.NewGuid().ToString();

var affected = await context.EntityWithMoreColumns.ExecuteUpdateAsync(x => x.SetProperty(c => c.Name, name));
Console.WriteLine($"Number of affected rows: {affected}, expected: 1");

public class BlogContext : DbContext
{
    public DbSet<EntityWithOnlyId> EntityWithOnlyId { get; set; }
    public DbSet<EntityWithMoreColumns> EntityWithMoreColumns { get; set; }

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class EntityWithMoreColumns
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class EntityWithOnlyId
{
    public int Id { get; set; }
}

[cincuranet]

@roji Sure.