Translate Min/Max over inline collection via LEAST/GREATEST

[roji]

Given the following query:

_ = context.Blogs
    .Select(b => new[] { b.Updated, b.Updated2 }.Max())
    .ToList();

Full code

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

_ = context.Blogs
    .Select(b => new[] { b.Updated, b.Updated2 }.Max())
    .ToList();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { 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 Blog
{
    public int Id { get; set; }
    public DateTime Updated { get; set; }
    public DateTime Updated2 { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public DateTime Updated { get; set; }

    public Blog Blog { get; set; }
}

EF 8.0 generates the following SQL:

SELECT (
    SELECT MAX([v].[Value])
    FROM (VALUES ([b].[Updated]), ([b].[Updated2])) AS [v]([Value]))
FROM [Blogs] AS [b]

We should recognize Min/Max over an inline collection, and just translate to LEAST/GREATEST:

SELECT GREATEST([b].[Updated], [b].[Updated2])
FROM [Blogs] AS [b]

Note that #31681 already tracks adding EF.Functions.{Least,Greatest} for users to invoke directly; this issue tracks changing the Min/Max translation.

[roji]

As per team decision, EF will always assume the latest SQL Server is being used; this means this is a breaking change, as queries using Max() over inline collections will start breaking for users.

[ErikEJ]

@roji Interestingly, this also works on a SQL Server 2022 with a database a compat level 100:

SELECT GREATEST('6.62', 3.1415, N'7') AS GreatestVal;

[roji]

Huh, yeah, that's a bit odd...!

[ErikEJ]

I think it is by design - https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#differences-between-compatibility-levels

"New Transact-SQL syntax isn't gated by database compatibility level, except when they can break existing applications by creating a conflict with user Transact-SQL code. These exceptions are documented in the next sections of this article that outline the differences between specific compatibility levels."

Probably OPENJSON is an exception: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16