Updating an indexed field is case-insensitive

[ValeriyGourov]

Sample

Program

await using BloggingContext db = new();

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

Blog newBlog = new()
{
	Title = "Test",
	Url = "http://blogs.msdn.com/ADOnet"
};
db.Add(newBlog);
await db.SaveChangesAsync();

int blogId = newBlog.Id;

Blog blog = await db.Blogs
	.Where(blog => blog.Id == blogId)
	.FirstAsync();

blog.Title = blog.Title.ToUpperInvariant();
blog.Url = blog.Url.ToUpperInvariant();
await db.SaveChangesAsync();

Blog

public class Blog
{
	public int Id { get; set; }
	public string Url { get; set; }
	public string Title { get; set; }
}

BloggingContext

public class BloggingContext : DbContext
{
	public DbSet<Blog> Blogs { get; set; }

	protected override void OnConfiguring(DbContextOptionsBuilder options)
	{
		_ = options.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EfSaveChangesCaseInsensitiveError8");

		options
			.EnableSensitiveDataLogging()
			.LogTo(Console.WriteLine)
			.LogTo(sql => Debug.WriteLine(sql));
	}

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder
			.Entity<Blog>()
			.HasIndex(blog => blog.Url)
			/*.IsUnique()*/;
	}
}

After the second invocation of SaveChangesAsync() we have different generated SQL-scripts in different versions of EF:

Entity Framework 7

info: 18.12.2023 15:02:20.892 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (8ms) [Parameters=[@p2='1', @p0='TEST' (Nullable = false) (Size = 4000), @p1='HTTP://BLOGS.MSDN.COM/ADONET' (Nullable = false) (Size = 450)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      UPDATE [Blogs] SET [Title] = @p0, [Url] = @p1
      OUTPUT 1
      WHERE [Id] = @p2;

Entity Framework 8

info: 18.12.2023 15:03:02.753 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (4ms) [Parameters=[@p1='1', @p0='TEST' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      UPDATE [Blogs] SET [Title] = @p0
      OUTPUT 1
      WHERE [Id] = @p1;

So, when we only change the case of an indexed field (Url), the UPDATE operation doesn't include that field in the script:

UPDATE [Blogs] SET [Title] = @p0, [Url] = @p1

UPDATE [Blogs] SET [Title] = @p0

I didn't find any explanation for this new behavior in the documentation. Is this a bug (I think/hope it is) or an unmentioned breaking change?

Provider and version information

EF Core version: 8.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8
Operating system: Windows 11 Pro
IDE: Visual Studio 2022 17.8.3

[ajcvickers]

Tracked by #32898

[ValeriyGourov]

@ajcvickers

Sample

Let's go further. Let's say we change Program:

await using BloggingContext db = new();

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

Blog newBlog = new()
{
	Title = "Test",
	Url = "http://blogs.msdn.com/ADOnet"
};
db.Add(newBlog);
await db.SaveChangesAsync();

int blogId = newBlog.Id;

Blog blog = await db.Blogs
	.Where(blog => blog.Id == blogId)
	.FirstAsync();

blog.Title = blog.Title.ToUpperInvariant();
blog.Url = blog.Url.ToUpperInvariant();
await db.SaveChangesAsync();

Blog blogTracked = await db.Blogs
	.Where(blog => blog.Id == blogId)
	.FirstAsync();
Console.WriteLine($"Tracked: {blogTracked.Url}");

Blog blogUntracked = await db.Blogs
	.AsNoTracking()
	.Where(blog => blog.Id == blogId)
	.FirstAsync();
Console.WriteLine($"Untracked: {blogUntracked.Url}");

Before the second invocation of SaveChangesAsync() I see in db.ChangeTracker.DebugView.LongView:

Blog {Id: 1} Unchanged
    Id: 1 PK
    Title: 'TEST' Originally 'Test'
    Url: 'HTTP://BLOGS.MSDN.COM/ADONET' Originally 'http://blogs.msdn.com/ADOnet'

After invocation:

Blog {Id: 1} Unchanged
    Id: 1 PK
    Title: 'TEST'
    Url: 'HTTP://BLOGS.MSDN.COM/ADONET'

That is, we have blogTracked.Url == "HTTP://BLOGS.MSDN.COM/ADONET". And if/when we query an untracked entity, we have blogUntracked.Url == "http://blogs.msdn.com/ADOnet". But I'm still working with the blog instance and at this point I'm sure the Url field has been modified in the database. In my opinion, we have inconsistent data. And this is not about case-insensitive string key comparisons on SQL Server because in this situation we are not comparing values, we just want to modify our value in the database. I still think this is a bug and not a breaking change. In this situation, I should know/remember about this EF behavior, so perhaps there should be some kind of warning or even runtime error?

[ajcvickers]

Closing in favor of #32898.