GroupBy element projection evaluates a conversion around g.Key on the client instead of as a SQL CAST #39367

Bug description

Since #38963 ("Stream GroupBy elements instead of joining the source back to itself"), a projection that enumerates a grouping's elements is translated as a single scan. The groupings are then assembled on the client, and the rest of the projection runs as a client-side selector over each materialized grouping.

That client-side selector accepts a conversion around the key ((decimal)g.Key, (int)g.Key, ...). The conversion therefore now runs in .NET, whereas before #38963 it was translated to a SQL CAST with the database's semantics. The two disagree:

• (decimal) over a double key. SQL Server's CAST(... AS decimal(18,2)) rounds to the mapped scale (1.24). .NET's (decimal)double keeps every digit of the double (1.2370000000000000994759830064). On SQLite, the old translation read the value through CAST(... AS TEXT) (1.237). The same key projected next to an aggregate (g.Count()) is still cast on the server, so two projections of the same key in the same model now disagree.
• (int) over a long key that doesn't fit. SQL Server raised Arithmetic overflow error converting expression to data type int (SQLite: OverflowException). The lifted plan silently returns wrapped values (-1294967296), because the C# conversion is unchecked.
• (int) over a money key (SQL Server). CAST(money AS int) rounds (2.5 → 3), while .NET's (int)decimal truncates (2). Northwind's Products.UnitPrice shows this: GroupBy(p => p.UnitPrice).Select(g => new { Key = (int?)g.Key, Ids = g.Select(p => p.ProductID).ToList() }) now returns different keys from the same projection with g.Count() in place of the list.
• (int) over an enum key stored as a string (HasConversion<string>()). The results change here too, although this time the new result is arguably the correct one. The old translation threw on SQL Server (CAST(N'A' AS int)) and returned 0 for every group on SQLite.

release/11.0 (without #38963) returns 1.24 / 2.50 for the first query and throws the overflow error for the third.

Your code

using Microsoft.EntityFrameworkCore;

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

context.Posts.AddRange(
    new Post { Id = 1, Rating = 1.237, Views = 3_000_000_000 },
    new Post { Id = 2, Rating = 1.237, Views = 3_000_000_000 },
    new Post { Id = 3, Rating = 2.5, Views = 1 });
await context.SaveChangesAsync();

// (decimal) over a double key, with the group's elements: the cast now runs on the client.
var withElements = context.Posts
    .GroupBy(p => p.Rating)
    .Select(g => new { Key = (decimal)g.Key, Ids = g.Select(p => p.Id).ToList() });
Console.WriteLine(withElements.ToQueryString());
foreach (var group in await withElements.ToListAsync())
{
    Console.WriteLine($"Key = {group.Key}, Ids = [{string.Join(", ", group.Ids)}]");
}

// The same key projected next to an aggregate is still cast on the server.
var withCount = context.Posts
    .GroupBy(p => p.Rating)
    .Select(g => new { Key = (decimal)g.Key, Count = g.Count() });
Console.WriteLine(withCount.ToQueryString());
foreach (var group in await withCount.ToListAsync())
{
    Console.WriteLine($"Key = {group.Key}, Count = {group.Count}");
}

// (int) over a long key that doesn't fit in an int: the server throws, the client silently wraps.
var narrowed = context.Posts
    .GroupBy(p => p.Views)
    .Select(g => new { Key = (int)g.Key, Ids = g.Select(p => p.Id).ToList() });
Console.WriteLine(narrowed.ToQueryString());
foreach (var group in await narrowed.ToListAsync())
{
    Console.WriteLine($"Key = {group.Key}, Ids = [{string.Join(", ", group.Ids)}]");
}

public class BlogContext : DbContext
{
    public DbSet<Post> Posts => Set<Post>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(@"Server=(localdb)\MSSQLLocalDB;Database=KeyConversionRepro;Trusted_Connection=True;TrustServerCertificate=True");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<Post>().Property(p => p.Id).ValueGeneratedNever();
}

public class Post
{
    public int Id { get; set; }
    public double Rating { get; set; }
    public long Views { get; set; }
}

Output on main (ff9c81ec97):

SELECT [p].[Rating], DENSE_RANK() OVER(ORDER BY [p].[Rating]), [p].[Id]
FROM [Posts] AS [p]
ORDER BY [p].[Rating]
Key = 1.2370000000000000994759830064, Ids = [1, 2]
Key = 2.5, Ids = [3]
SELECT CAST([p].[Rating] AS decimal(18,2)) AS [Key], COUNT(*) AS [Count]
FROM [Posts] AS [p]
GROUP BY [p].[Rating]
Key = 1.24, Count = 2
Key = 2.50, Count = 1
SELECT [p].[Views], DENSE_RANK() OVER(ORDER BY [p].[Views]), [p].[Id]
FROM [Posts] AS [p]
ORDER BY [p].[Views]
Key = 1, Ids = [3]
Key = -1294967296, Ids = [1, 2]

Output on release/11.0 (9e685c7a52), where these projections are not lifted:

Key = 1.24, Ids = [1, 2]
Key = 2.50, Ids = [3]
Key = 1.24, Count = 2
Key = 2.50, Count = 1
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.

The same happens with UseSqlite(...). There, (decimal)g.Key was 1.237 before (read through CAST(... AS TEXT)) and is 1.2370000000000000994759830064 now, and (int)g.Key threw OverflowException before and wraps now.

Stack traces

Verbose output

EF Core version

12.0.0-alpha.1

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response